From h.otto@freemail.hu Sun Dec  11 00:53:40 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id DB8DD9DCC09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 20:53:40 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 76249-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 20:53:43 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from viefep12-int.chello.at (viefep12-int.chello.at [213.46.255.25])
	by postgresql.org (Postfix) with ESMTP id 1A4B99DCBBD
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 20:53:37 -0400 (AST)
Received: from OTTO ([213.222.166.137]) by viefep12-int.chello.at
	(InterMail vM.6.01.04.04 201-2131-118-104-20050224) with SMTP
	id <20051211005339.JESQ25653.viefep12-int.chello.at@OTTO>
	for <pgsql-sql@postgresql.org>; Sun, 11 Dec 2005 01:53:39 +0100
Message-ID: <003001c5fded$53a2c5a0$0200a8c0@OTTO>
From: =?iso-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
To: <pgsql-sql@postgresql.org>
References: <20051209053849.498E8F0BEC@svr2.postgresql.org>
Subject: select count of distinct rows
Date: Sun, 11 Dec 2005 01:53:40 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_002D_01C5FDF5.B54CEEE0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001]
X-Spam-Score: 0.001
X-Spam-Level: 
X-Archive-Number: 200512/75
X-Sequence-Number: 23617

This is a multi-part message in MIME format.

------=_NextPart_000_002D_01C5FDF5.B54CEEE0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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
------=_NextPart_000_002D_01C5FDF5.B54CEEE0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns=3D"http://www.w3.org/TR/REC-html40" xmlns:o =3D=20
"urn:schemas-microsoft-com:office:office" xmlns:w =3D=20
"urn:schemas-microsoft-com:office:word"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2769" name=3DGENERATOR>
<STYLE>@font-face {
	font-family: Verdana;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
	COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
	COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
	COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
	COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
	FONT-WEIGHT: normal; COLOR: windowtext; FONT-STYLE: normal; =
FONT-FAMILY: Verdana; TEXT-DECORATION: none; mso-style-type: =
personal-compose
}
DIV.Section1 {
	page: Section1
}
</STYLE>
</HEAD>
<BODY lang=3DEN-US vLink=3Dpurple link=3Dblue bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2><SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier =
New'"></SPAN></FONT><FONT=20
face=3DVerdana size=3D2><SPAN style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Verdana"><o:p>I=20
would like to select the count of distinct rows in a=20
table.</o:p></SPAN></FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2><SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Verdana"><o:p></o:p></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DVerdana size=3D2><SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Verdana"><o:p>SELECT =
COUNT(DISTINCT *) FROM=20
mytable;</o:p></SPAN></FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2><SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Verdana"><o:p></o:p></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DVerdana size=3D2><SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Verdana"><o:p>This does not work. =
How can I=20
do it with Postgres?</o:p></SPAN></FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2><SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Verdana"><o:p></o:p></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DVerdana size=3D2><SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Verdana"><o:p>Thanks,</o:p></SPAN></FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2><SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Verdana"><o:p>Otto</o:p></SPAN></FONT></DIV></BODY></HTML>

------=_NextPart_000_002D_01C5FDF5.B54CEEE0--





From systemguards@gmail.com Sun Dec  11 00:57:11 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 5521B9DCD4F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 20:57:11 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 78013-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 20:57:12 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196])
	by postgresql.org (Postfix) with ESMTP id 349129DCC09
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 20:57:06 -0400 (AST)
Received: by wproxy.gmail.com with SMTP id 69so1166296wra
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 16:57:10 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=FU5VJZmSIsTzhWqJ+TEFMImnN5CppfXWoLqhJLKHvnFIjEOvGVhdGDT0EGO3IEayQa9KCE+RCoqN0quzcF+T5DQ7jrUEVeb3Ds4lQQHbRSxeoijBl/PxlV27hqQqX/+yg3BfJzhE/LeeZ53kN7vZJyyF18UtCQAg3HMimJcjqWs=
Received: by 10.54.117.11 with SMTP id p11mr6566073wrc;
	Sat, 10 Dec 2005 16:57:10 -0800 (PST)
Received: by 10.54.98.6 with HTTP; Sat, 10 Dec 2005 16:57:10 -0800 (PST)
Message-ID: <c2d9e70e0512101657o50263342ma1769631b3a67133@mail.gmail.com>
Date: Sat, 10 Dec 2005 19:57:10 -0500
From: Jaime Casanova <systemguards@gmail.com>
To: =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
Subject: Re: select count of distinct rows
Cc: pgsql-sql@postgresql.org
In-Reply-To: <003001c5fded$53a2c5a0$0200a8c0@OTTO>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <20051209053849.498E8F0BEC@svr2.postgresql.org>
	<003001c5fded$53a2c5a0$0200a8c0@OTTO>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.138 required=5 tests=[AWL=0.138]
X-Spam-Score: 0.138
X-Spam-Level: 
X-Archive-Number: 200512/76
X-Sequence-Number: 23618

On 12/10/05, Havasv=F6lgyi Ott=F3 <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 ;)



From h.otto@freemail.hu Sun Dec  11 01:07:03 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 30FED9DCAE5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:07:03 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 78003-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:07:06 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from viefep16-int.chello.at (viefep16-int.chello.at [213.46.255.17])
	by postgresql.org (Postfix) with ESMTP id 2634C9DCAB8
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 21:06:59 -0400 (AST)
Received: from OTTO ([213.222.166.137]) by viefep16-int.chello.at
	(InterMail vM.6.01.04.04 201-2131-118-104-20050224) with SMTP
	id <20051211010700.MLVS21578.viefep16-int.chello.at@OTTO>;
	Sun, 11 Dec 2005 02:07:00 +0100
Message-ID: <005501c5fdef$30be34f0$0200a8c0@OTTO>
From: =?iso-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
To: "Jaime Casanova" <systemguards@gmail.com>
Cc: <pgsql-sql@postgresql.org>
References: <20051209053849.498E8F0BEC@svr2.postgresql.org>
	<003001c5fded$53a2c5a0$0200a8c0@OTTO>
	<c2d9e70e0512101657o50263342ma1769631b3a67133@mail.gmail.com>
Subject: Re: select count of distinct rows
Date: Sun, 11 Dec 2005 02:07:00 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.001 required=5 tests=[AWL=0.001]
X-Spam-Score: 0.001
X-Spam-Level: 
X-Archive-Number: 200512/77
X-Sequence-Number: 23619

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






From systemguards@gmail.com Sun Dec  11 01:12:39 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id B618A9DCAE5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:12:39 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 77725-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:12:43 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.199])
	by postgresql.org (Postfix) with ESMTP id B31219DCAB8
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 21:12:37 -0400 (AST)
Received: by wproxy.gmail.com with SMTP id i21so1023982wra
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 17:12:41 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=WKLd0L3ELzswKa2UPklzceAIRx0XppuPWL7YGS3yFjixK2I0nKs4JicD7Hu3rpHSPabmk4txgpAC/Xn/MxWb2yvmZTW1uaTVDYxWtAV8n4mKmHPSR0ugqIuED+OsnhtEmKiUvA0xMhYfSX7LDUCcjKy6i1rq1GRwy02lz38aMbg=
Received: by 10.54.153.18 with SMTP id a18mr4991383wre;
	Sat, 10 Dec 2005 17:12:40 -0800 (PST)
Received: by 10.54.98.6 with HTTP; Sat, 10 Dec 2005 17:12:41 -0800 (PST)
Message-ID: <c2d9e70e0512101712u760f3eb4x3f5b0820233d0530@mail.gmail.com>
Date: Sat, 10 Dec 2005 20:12:41 -0500
From: Jaime Casanova <systemguards@gmail.com>
To: "Foster, Stephen" <stephenlfoster@comcast.net>
Subject: Re: select count of distinct rows
Cc: =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>,
	pgsql-sql@postgresql.org
In-Reply-To: <005c01c5fdf0$01b90c10$2101a8c0@cfgod>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <005501c5fdef$30be34f0$0200a8c0@OTTO>
	<005c01c5fdf0$01b90c10$2101a8c0@cfgod>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.136 required=5 tests=[AWL=0.136]
X-Spam-Score: 0.136
X-Spam-Level: 
X-Archive-Number: 200512/79
X-Sequence-Number: 23621

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 ;)



From stephenlfoster@comcast.net Sun Dec  11 01:10:54 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 5CD8B9DCAE5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:10:54 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 79545-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:10:57 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sccrmhc12.comcast.net (sccrmhc12.comcast.net [63.240.77.82])
	by postgresql.org (Postfix) with ESMTP id 27DF39DCAB8
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 21:10:51 -0400 (AST)
Received: from cfgod (pcp08670491pcs.500ash01.tn.comcast.net[69.137.84.227])
	by comcast.net (sccrmhc12) with SMTP
	id <2005121101092401200lm0v2e>; Sun, 11 Dec 2005 01:09:25 +0000
From: "Foster, Stephen" <stephenlfoster@comcast.net>
To: =?iso-8859-1?Q?'Havasv=F6lgyi_Ott=F3'?= <h.otto@freemail.hu>,
	"'Jaime Casanova'" <systemguards@gmail.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: select count of distinct rows
Date: Sat, 10 Dec 2005 19:12:49 -0600
Message-ID: <005c01c5fdf0$01b90c10$2101a8c0@cfgod>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.2616
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
In-Reply-To: <005501c5fdef$30be34f0$0200a8c0@OTTO>
Importance: Normal
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/78
X-Sequence-Number: 23620

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=F6lgyi =
Ott=F3
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=20
impossible?

Thanks,
Otto

----- Original Message -----=20
From: "Jaime Casanova" <systemguards@gmail.com>
To: "Havasv=F6lgyi Ott=F3" <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=F6lgyi Ott=F3 <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

--=20
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
=20

--=20
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
=20




From systemguards@gmail.com Sun Dec  11 01:16:20 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 66ECF9DCBBD
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:16:20 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 81611-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:16:23 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.195])
	by postgresql.org (Postfix) with ESMTP id 6E51A9DCBE1
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 21:16:18 -0400 (AST)
Received: by wproxy.gmail.com with SMTP id i34so1207514wra
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 17:16:22 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=TkBMWv9N74Ajhni72xDYOdCxBPuc1CI0PHq+i2I+dRV+8/wg6KNYwSkpB4HcDksnwqVIe/Dn0Xa4VQdKyIVVVomMb89FCRvR3r1LxqLI0D1Sg4ecwrIDsB2xhWC+EqyjDGoTCRJ2E54Y3hGm65yeR7tB03ykLuWs1TGVHb1CIJA=
Received: by 10.54.142.10 with SMTP id p10mr6562362wrd;
	Sat, 10 Dec 2005 17:16:22 -0800 (PST)
Received: by 10.54.98.6 with HTTP; Sat, 10 Dec 2005 17:16:22 -0800 (PST)
Message-ID: <c2d9e70e0512101716v396519a9s5d0ed9f4437d2d5c@mail.gmail.com>
Date: Sat, 10 Dec 2005 20:16:22 -0500
From: Jaime Casanova <systemguards@gmail.com>
To: =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
Subject: Re: select count of distinct rows
Cc: pgsql-sql@postgresql.org
In-Reply-To: <003001c5fded$53a2c5a0$0200a8c0@OTTO>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <20051209053849.498E8F0BEC@svr2.postgresql.org>
	<003001c5fded$53a2c5a0$0200a8c0@OTTO>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.134 required=5 tests=[AWL=0.134]
X-Spam-Score: 0.134
X-Spam-Level: 
X-Archive-Number: 200512/80
X-Sequence-Number: 23622

On 12/10/05, Havasv=F6lgyi Ott=F3 <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 ;)



From h.otto@freemail.hu Sun Dec  11 01:17:14 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 09B2B9DCAE5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:17:14 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 80686-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:17:17 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from viefep17-int.chello.at (viefep17-int.chello.at [213.46.255.23])
	by postgresql.org (Postfix) with ESMTP id 4AA269DCAB8
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 21:17:10 -0400 (AST)
Received: from OTTO ([213.222.166.137]) by viefep17-int.chello.at
	(InterMail vM.6.01.04.04 201-2131-118-104-20050224) with SMTP
	id <20051211011713.VBSZ15321.viefep17-int.chello.at@OTTO>;
	Sun, 11 Dec 2005 02:17:13 +0100
Message-ID: <006601c5fdf0$9e2f5e50$0200a8c0@OTTO>
From: =?iso-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
To: "Foster, Stephen" <stephenlfoster@comcast.net>,
	"'Jaime Casanova'" <systemguards@gmail.com>
Cc: <pgsql-sql@postgresql.org>
References: <005c01c5fdf0$01b90c10$2101a8c0@cfgod>
Subject: Re: select count of distinct rows
Date: Sun, 11 Dec 2005 02:17:13 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/81
X-Sequence-Number: 23623

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






From fbax@sympatico.ca Sun Dec  11 01:23:56 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 1278E9DCC09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:23:56 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 82227-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:23:59 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from tomts36-srv.bellnexxia.net (tomts36.bellnexxia.net
	[209.226.175.93])
	by postgresql.org (Postfix) with ESMTP id 712519DCC78
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 21:23:53 -0400 (AST)
Received: from acer.sympatico.ca ([65.95.186.80]) by
 tomts36-srv.bellnexxia.net
	(InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id
	<20051211012356.GBQF5032.tomts36-srv.bellnexxia.net@acer.sympatico.ca>
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 20:23:56 -0500
Message-Id: <5.2.1.1.0.20051210202158.0344ac80@pop6.sympatico.ca>
X-Sender: b1wudu74@pop6.sympatico.ca
X-Mailer: QUALCOMM Windows Eudora Version 5.2.1
Date: Sat, 10 Dec 2005 20:23:14 -0500
To: <pgsql-sql@postgresql.org>
From: Frank Bax <fbax@sympatico.ca>
Subject: Re: select count of distinct rows
In-Reply-To: <003001c5fded$53a2c5a0$0200a8c0@OTTO>
References: <20051209053849.498E8F0BEC@svr2.postgresql.org>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable
X-Originating-IP: [0]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/82
X-Sequence-Number: 23624

At 07:53 PM 12/10/05, Havasv=F6lgyi Ott=F3 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;




From h.otto@freemail.hu Sun Dec  11 01:25:20 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 3F8749DCD60
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:25:20 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 84150-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:25:23 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from viefep20-int.chello.at (viefep12-int.chello.at [213.46.255.25])
	by postgresql.org (Postfix) with ESMTP id 878479DD883
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 21:25:17 -0400 (AST)
Received: from OTTO ([213.222.166.137]) by viefep20-int.chello.at
	(InterMail vM.6.01.04.04 201-2131-118-104-20050224) with SMTP
	id <20051211012520.HICN24926.viefep20-int.chello.at@OTTO>;
	Sun, 11 Dec 2005 02:25:20 +0100
Message-ID: <007101c5fdf1$c03c7360$0200a8c0@OTTO>
From: =?iso-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
To: "Jaime Casanova" <systemguards@gmail.com>
Cc: <pgsql-sql@postgresql.org>
References: <20051209053849.498E8F0BEC@svr2.postgresql.org>
	<003001c5fded$53a2c5a0$0200a8c0@OTTO>
	<c2d9e70e0512101716v396519a9s5d0ed9f4437d2d5c@mail.gmail.com>
Subject: Re: select count of distinct rows
Date: Sun, 11 Dec 2005 02:25:20 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/84
X-Sequence-Number: 23626

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 ;)






From stephenlfoster@comcast.net Sun Dec  11 01:24:37 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id B943D9DCAE5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:24:37 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 80686-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:24:41 -0400 (AST)
X-Greylist: delayed 00:15:13.316731 by SQLgrey-
Received: from sccrmhc12.comcast.net (sccrmhc12.comcast.net [204.127.202.56])
	by postgresql.org (Postfix) with ESMTP id 9D1859DCAB8
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 21:24:35 -0400 (AST)
Received: from cfgod (pcp08670491pcs.500ash01.tn.comcast.net[69.137.84.227])
	by comcast.net (sccrmhc12) with SMTP
	id <2005121101243701200loelhe>; Sun, 11 Dec 2005 01:24:38 +0000
From: "Foster, Stephen" <stephenlfoster@comcast.net>
To: "'Jaime Casanova'" <systemguards@gmail.com>
Cc: =?iso-8859-1?Q?'Havasv=F6lgyi_Ott=F3'?= <h.otto@freemail.hu>,
	<pgsql-sql@postgresql.org>
Subject: Re: select count of distinct rows
Date: Sat, 10 Dec 2005 19:28:03 -0600
Message-ID: <005d01c5fdf2$21d1fcd0$2101a8c0@cfgod>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.2616
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
In-Reply-To: <c2d9e70e0512101712u760f3eb4x3f5b0820233d0530@mail.gmail.com>
Importance: Normal
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.159 required=5 tests=[AWL=-1.159,
	DNS_FROM_RFC_POST=1.44, DNS_FROM_RFC_WHOIS=0.879]
X-Spam-Score: 1.159
X-Spam-Level: *
X-Archive-Number: 200512/83
X-Sequence-Number: 23625

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=F6lgyi Ott=F3; 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

--=20
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
=20

--=20
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
=20




From h.otto@freemail.hu Sun Dec  11 01:29:26 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id E5F3A9DCAE5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:29:26 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 85954-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Dec 2005 21:29:30 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from viefep18-int.chello.at (viefep18-int.chello.at [213.46.255.21])
	by postgresql.org (Postfix) with ESMTP id 2C4709DCAB8
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2005 21:29:23 -0400 (AST)
Received: from OTTO ([213.222.166.137]) by viefep18-int.chello.at
	(InterMail vM.6.01.04.04 201-2131-118-104-20050224) with SMTP
	id <20051211012926.NLJX25506.viefep18-int.chello.at@OTTO>;
	Sun, 11 Dec 2005 02:29:26 +0100
Message-ID: <008601c5fdf2$5332d380$0200a8c0@OTTO>
From: =?iso-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
To: <pgsql-sql@postgresql.org>,
	"Frank Bax" <fbax@sympatico.ca>
References: <20051209053849.498E8F0BEC@svr2.postgresql.org>
	<5.2.1.1.0.20051210202158.0344ac80@pop6.sympatico.ca>
Subject: Re: select count of distinct rows
Date: Sun, 11 Dec 2005 02:29:26 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/85
X-Sequence-Number: 23627

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