Thread: unsubscribe
<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>
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
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 ;)
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
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
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 ;)
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 ;)
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
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;
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
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 ;)
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