pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect count of distinct rows
11+ messages / 4 participants
[nested] [flat]
* select count of distinct rows
@ 2005-12-11 00:53 ` Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 00:57 ` Re: select count of distinct rows Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:16 ` Re: select count of distinct rows Jaime Casanova <systemguards@gmail.com>
1 sibling, 2 replies; 11+ messages in thread
From: Havasvölgyi Ottó @ 2005-12-11 00:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select count of distinct rows
2005-12-11 00:53 ` select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
@ 2005-12-11 00:57 ` Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:07 ` Re: select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
1 sibling, 1 reply; 11+ messages in thread
From: Jaime Casanova @ 2005-12-11 00:57 UTC (permalink / raw)
To: Havasvölgyi Ottó <h.otto@freemail.hu>; +Cc: pgsql-sql
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 ;)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select count of distinct rows
2005-12-11 00:53 ` select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 00:57 ` Re: select count of distinct rows Jaime Casanova <systemguards@gmail.com>
@ 2005-12-11 01:07 ` Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 01:12 ` Re: select count of distinct rows Foster, Stephen <stephenlfoster@comcast.net>
0 siblings, 1 reply; 11+ messages in thread
From: Havasvölgyi Ottó @ 2005-12-11 01:07 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select count of distinct rows
2005-12-11 00:53 ` select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 00:57 ` Re: select count of distinct rows Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:07 ` Re: select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
@ 2005-12-11 01:12 ` Foster, Stephen <stephenlfoster@comcast.net>
2005-12-11 01:12 ` Re: select count of distinct rows Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:17 ` Re: select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
0 siblings, 2 replies; 11+ messages in thread
From: Foster, Stephen @ 2005-12-11 01:12 UTC (permalink / raw)
To: 'Havasvölgyi Ottó' <h.otto@freemail.hu>; 'Jaime Casanova' <systemguards@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select count of distinct rows
2005-12-11 00:53 ` select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 00:57 ` Re: select count of distinct rows Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:07 ` Re: select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 01:12 ` Re: select count of distinct rows Foster, Stephen <stephenlfoster@comcast.net>
@ 2005-12-11 01:12 ` Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:28 ` Re: select count of distinct rows Foster, Stephen <stephenlfoster@comcast.net>
1 sibling, 1 reply; 11+ messages in thread
From: Jaime Casanova @ 2005-12-11 01:12 UTC (permalink / raw)
To: Foster, Stephen <stephenlfoster@comcast.net>; +Cc: Havasvölgyi Ottó <h.otto@freemail.hu>; pgsql-sql
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 ;)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select count of distinct rows
2005-12-11 00:53 ` select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 00:57 ` Re: select count of distinct rows Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:07 ` Re: select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 01:12 ` Re: select count of distinct rows Foster, Stephen <stephenlfoster@comcast.net>
2005-12-11 01:12 ` Re: select count of distinct rows Jaime Casanova <systemguards@gmail.com>
@ 2005-12-11 01:28 ` Foster, Stephen <stephenlfoster@comcast.net>
0 siblings, 0 replies; 11+ messages in thread
From: Foster, Stephen @ 2005-12-11 01:28 UTC (permalink / raw)
To: 'Jaime Casanova' <systemguards@gmail.com>; +Cc: 'Havasvölgyi Ottó' <h.otto@freemail.hu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select count of distinct rows
2005-12-11 00:53 ` select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 00:57 ` Re: select count of distinct rows Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:07 ` Re: select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 01:12 ` Re: select count of distinct rows Foster, Stephen <stephenlfoster@comcast.net>
@ 2005-12-11 01:17 ` Havasvölgyi Ottó <h.otto@freemail.hu>
1 sibling, 0 replies; 11+ messages in thread
From: Havasvölgyi Ottó @ 2005-12-11 01:17 UTC (permalink / raw)
To: Foster, Stephen <stephenlfoster@comcast.net>; 'Jaime Casanova' <systemguards@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select count of distinct rows
2005-12-11 00:53 ` select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
@ 2005-12-11 01:16 ` Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:25 ` Re: select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
1 sibling, 1 reply; 11+ messages in thread
From: Jaime Casanova @ 2005-12-11 01:16 UTC (permalink / raw)
To: Havasvölgyi Ottó <h.otto@freemail.hu>; +Cc: pgsql-sql
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 foo
GROUP BY fld1;
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select count of distinct rows
2005-12-11 00:53 ` select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 01:16 ` Re: select count of distinct rows Jaime Casanova <systemguards@gmail.com>
@ 2005-12-11 01:25 ` Havasvölgyi Ottó <h.otto@freemail.hu>
0 siblings, 0 replies; 11+ messages in thread
From: Havasvölgyi Ottó @ 2005-12-11 01:25 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; +Cc: pgsql-sql
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 foo
GROUP BY fld1;
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select count of distinct rows
@ 2005-12-11 01:23 ` Frank Bax <fbax@sympatico.ca>
2005-12-11 01:29 ` Re: select count of distinct rows Havasvölgyi Ottó <h.otto@freemail.hu>
1 sibling, 1 reply; 11+ messages in thread
From: Frank Bax @ 2005-12-11 01:23 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select count of distinct rows
2005-12-11 01:23 ` Re: select count of distinct rows Frank Bax <fbax@sympatico.ca>
@ 2005-12-11 01:29 ` Havasvölgyi Ottó <h.otto@freemail.hu>
0 siblings, 0 replies; 11+ messages in thread
From: Havasvölgyi Ottó @ 2005-12-11 01:29 UTC (permalink / raw)
To: pgsql-sql; Frank Bax <fbax@sympatico.ca>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-12-11 01:29 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-11 00:53 ` Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 00:57 ` Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:07 ` Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 01:12 ` Foster, Stephen <stephenlfoster@comcast.net>
2005-12-11 01:12 ` Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:28 ` Foster, Stephen <stephenlfoster@comcast.net>
2005-12-11 01:17 ` Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 01:16 ` Jaime Casanova <systemguards@gmail.com>
2005-12-11 01:25 ` Havasvölgyi Ottó <h.otto@freemail.hu>
2005-12-11 01:23 ` Frank Bax <fbax@sympatico.ca>
2005-12-11 01:29 ` Havasvölgyi Ottó <h.otto@freemail.hu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox