Thread: unsubscribe

unsubscribe

From
"Dinesh Pandey"
Date:
<div class="Section1"><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt;
font-family:"Courier New"">unsubscribe</span></font><font face="Verdana" size="2"><span
style="font-size:10.0pt;font-family:Verdana"></span></font></div>

select count of distinct rows

From
Havasvölgyi Ottó
Date:
Hi,
 
I would like to select the count of distinct rows in a table.
 
SELECT COUNT(DISTINCT *) FROM mytable;
 
This does not work. How can I do it with Postgres?
 
Thanks,
Otto

Re: select count of distinct rows

From
Jaime Casanova
Date:
On 12/10/05, Havasvölgyi Ottó <h.otto@freemail.hu> wrote:
> Hi,
>
> I would like to select the count of distinct rows in a table.
>
> SELECT COUNT(DISTINCT *) FROM mytable;
>

are really all the fields distincts? the table doesn't have a pk?

> This does not work. How can I do it with Postgres?
>
> Thanks,
> Otto
>


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)


Re: select count of distinct rows

From
Havasvölgyi Ottó
Date:
Hi,

Yes, I need to compare all fields. Well, in this case it does not. Is this 
impossible?

Thanks,
Otto

----- Original Message ----- 
From: "Jaime Casanova" <systemguards@gmail.com>
To: "Havasvölgyi Ottó" <h.otto@freemail.hu>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, December 11, 2005 1:57 AM
Subject: Re: [SQL] select count of distinct rows


On 12/10/05, Havasvölgyi Ottó <h.otto@freemail.hu> wrote:
> Hi,
>
> I would like to select the count of distinct rows in a table.
>
> SELECT COUNT(DISTINCT *) FROM mytable;
>

are really all the fields distincts? the table doesn't have a pk?

> This does not work. How can I do it with Postgres?
>
> Thanks,
> Otto
>


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org





Re: select count of distinct rows

From
"Foster, Stephen"
Date:
Well this should work but I tried it and it didn't.

SELECT DISTINCT COUNT(*) FROM mytable;

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Havasvölgyi Ottó
Sent: Saturday, December 10, 2005 7:07 PM
To: Jaime Casanova
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] select count of distinct rows

Hi,

Yes, I need to compare all fields. Well, in this case it does not. Is
this
impossible?

Thanks,
Otto

----- Original Message -----
From: "Jaime Casanova" <systemguards@gmail.com>
To: "Havasvölgyi Ottó" <h.otto@freemail.hu>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, December 11, 2005 1:57 AM
Subject: Re: [SQL] select count of distinct rows


On 12/10/05, Havasvölgyi Ottó <h.otto@freemail.hu> wrote:
> Hi,
>
> I would like to select the count of distinct rows in a table.
>
> SELECT COUNT(DISTINCT *) FROM mytable;
>

are really all the fields distincts? the table doesn't have a pk?

> This does not work. How can I do it with Postgres?
>
> Thanks,
> Otto
>


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005



Re: select count of distinct rows

From
Jaime Casanova
Date:
On 12/10/05, Foster, Stephen <stephenlfoster@comcast.net> wrote:
> Well this should work but I tried it and it didn't.
>
> SELECT DISTINCT COUNT(*) FROM mytable;
>

No, it shouldn't work... actually is a non-sense, count will return
just one value so there is nothing to be distinct with...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)


Re: select count of distinct rows

From
Jaime Casanova
Date:
On 12/10/05, Havasvölgyi Ottó <h.otto@freemail.hu> wrote:
> Hi,
>
> I would like to select the count of distinct rows in a table.
>
> SELECT COUNT(DISTINCT *) FROM mytable;
>
> This does not work. How can I do it with Postgres?
>
> Thanks,
> Otto
>

I guess what you need is to know how many times a single row is
duplicated so i think what you need is something like this:

SELECT fld1, COUNT(DISTINCT fld1)  FROM (SELECT ROW(*) as fld1 FROM mytable) AS fooGROUP BY fld1;

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)


Re: select count of distinct rows

From
Havasvölgyi Ottó
Date:
Hi,

This works for me but the result is not that what is expected. I returns 
with the number of rows in the table, and the distinct has no effect because 
there is only one row in the result.

Best Regards,
Otto


----- Original Message ----- 
From: "Foster, Stephen" <stephenlfoster@comcast.net>
To: "'Havasvölgyi Ottó'" <h.otto@freemail.hu>; "'Jaime Casanova'" 
<systemguards@gmail.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, December 11, 2005 2:12 AM
Subject: Re: [SQL] select count of distinct rows


Well this should work but I tried it and it didn't.

SELECT DISTINCT COUNT(*) FROM mytable;

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Havasvölgyi Ottó
Sent: Saturday, December 10, 2005 7:07 PM
To: Jaime Casanova
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] select count of distinct rows

Hi,

Yes, I need to compare all fields. Well, in this case it does not. Is
this
impossible?

Thanks,
Otto

----- Original Message ----- 
From: "Jaime Casanova" <systemguards@gmail.com>
To: "Havasvölgyi Ottó" <h.otto@freemail.hu>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, December 11, 2005 1:57 AM
Subject: Re: [SQL] select count of distinct rows


On 12/10/05, Havasvölgyi Ottó <h.otto@freemail.hu> wrote:
> Hi,
>
> I would like to select the count of distinct rows in a table.
>
> SELECT COUNT(DISTINCT *) FROM mytable;
>

are really all the fields distincts? the table doesn't have a pk?

> This does not work. How can I do it with Postgres?
>
> Thanks,
> Otto
>


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org





Re: select count of distinct rows

From
Frank Bax
Date:
At 07:53 PM 12/10/05, Havasvölgyi Ottó wrote:
>I would like to select the count of distinct rows in a table.
>         SELECT COUNT(DISTINCT *) FROM mytable;
>This does not work. How can I do it with Postgres?


select count(*) from (select distinct * from mytable) as x;



Re: select count of distinct rows

From
"Foster, Stephen"
Date:
Well, I find the problem on my end.  I was working with a new database
that I forgot to filler yet.  Yes, it works.  Jaime, I think what he is
trying to do is get the record count.  I would agree that using that
statement is a bit much.  But using SELECT COUNT(*) FROM mytable; would
give the same thing and should run faster.

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Jaime Casanova
Sent: Saturday, December 10, 2005 7:13 PM
To: Foster, Stephen
Cc: Havasvölgyi Ottó; pgsql-sql@postgresql.org
Subject: Re: [SQL] select count of distinct rows

On 12/10/05, Foster, Stephen <stephenlfoster@comcast.net> wrote:
> Well this should work but I tried it and it didn't.
>
> SELECT DISTINCT COUNT(*) FROM mytable;
>

No, it shouldn't work... actually is a non-sense, count will return
just one value so there is nothing to be distinct with...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005



Re: select count of distinct rows

From
Havasvölgyi Ottó
Date:
Yes, almost. I need the list of all different rows.
It's syntax error at the *.
ROW(*)         ^

Otto


----- Original Message ----- 
From: "Jaime Casanova" <systemguards@gmail.com>
To: "Havasvölgyi Ottó" <h.otto@freemail.hu>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, December 11, 2005 2:16 AM
Subject: Re: select count of distinct rows


On 12/10/05, Havasvölgyi Ottó <h.otto@freemail.hu> wrote:
> Hi,
>
> I would like to select the count of distinct rows in a table.
>
> SELECT COUNT(DISTINCT *) FROM mytable;
>
> This does not work. How can I do it with Postgres?
>
> Thanks,
> Otto
>

I guess what you need is to know how many times a single row is
duplicated so i think what you need is something like this:

SELECT fld1, COUNT(DISTINCT fld1)  FROM (SELECT ROW(*) as fld1 FROM mytable) AS fooGROUP BY fld1;

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)





Re: select count of distinct rows

From
Havasvölgyi Ottó
Date:
Oh, that's it.
Thank you all very much.

Otto



----- Original Message ----- 
From: "Frank Bax" <fbax@sympatico.ca>
To: <pgsql-sql@postgresql.org>
Sent: Sunday, December 11, 2005 2:23 AM
Subject: Re: [SQL] select count of distinct rows


At 07:53 PM 12/10/05, Havasvölgyi Ottó wrote:
>I would like to select the count of distinct rows in a table.
>         SELECT COUNT(DISTINCT *) FROM mytable;
>This does not work. How can I do it with Postgres?


select count(*) from (select distinct * from mytable) as x;


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org