Received: from fileserver002.intecsystems.co.uk ([194.200.14.125]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2SFpmU86393 for ; Wed, 28 Mar 2001 10:51:49 -0500 (EST) (envelope-from Michael.Ansley@intec-telecom-systems.com) Received: from fileserver002.intecsystems.co.uk (unverified) by fileserver002.intecsystems.co.uk (Content Technologies SMTPRS 4.1.5) with ESMTP id ; Wed, 28 Mar 2001 16:58:14 +0100 Received: by FILESERVER002 with Internet Mail Service (5.5.2650.21) id ; Wed, 28 Mar 2001 16:58:14 +0100 Message-ID: <7F124BC48D56D411812500D0B747251480F4F1@FILESERVER002> From: Michael Ansley To: "'Dirk Lutzebaeck'" Cc: pgsql-sql@postgresql.org Subject: RE: counting distinct rows on more than one column Date: Wed, 28 Mar 2001 16:58:13 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: multipart/alternative ; boundary="----_=_NextPart_001_01C0B79F.E4C2D712" X-Archive-Number: 200103/578 X-Sequence-Number: 2248 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C0B79F.E4C2D712 Content-Type: text/plain; charset="iso-8859-1" SELECT count(*) FROM (SELECT DISTINCT a, b FROM t) AS t2; should give you what you want. MikeA >> -----Original Message----- >> From: Dirk Lutzebaeck [mailto:lutzeb@aeccom.com] >> Sent: 28 March 2001 16:11 >> To: pgsql-sql@postgresql.org >> Subject: [SQL] counting distinct rows on more than one column >> >> >> >> Hi, >> >> on 7.0.3 want to COUNT >> >> SELECT DISTINCT a,b FROM t; >> >> I can't find a solution because any combination with >> count with more than one column gives syntax errors. >> >> One solution would be to set a view: >> >> CREATE VIEW v AS SELECT DISTINCT a,b FROM t; >> >> and then >> >> SELECT count(a) FROM v >> >> but views don't support distinct in v7.0.3 >> >> Ok I could use a temporary table but my select distinct tends to give >> large results. >> >> Any clues? >> >> Dirk >> >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 1: subscribe and unsubscribe commands go to >> majordomo@postgresql.org >> _________________________________________________________________________ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __________________________________________________________________________ ------_=_NextPart_001_01C0B79F.E4C2D712 Content-Type: text/html; charset="iso-8859-1" RE: [SQL] counting distinct rows on more than one column

SELECT count(*) FROM (SELECT DISTINCT a, b FROM t) AS t2;

should give you what you want.


MikeA


>> -----Original Message-----
>> From: Dirk Lutzebaeck [mailto:lutzeb@aeccom.com]
>> Sent: 28 March 2001 16:11
>> To: pgsql-sql@postgresql.org
>> Subject: [SQL] counting distinct rows on more than one column
>>
>>
>>
>> Hi,
>>
>> on 7.0.3 want to COUNT
>>
>>   SELECT DISTINCT a,b FROM t;
>>
>> I can't find a solution because any combination with
>> count with more than one column gives syntax errors.
>>
>> One solution would be to set a view:
>>
>> CREATE VIEW v AS SELECT DISTINCT a,b FROM t;
>>
>> and then
>>
>> SELECT count(a) FROM v
>>
>> but views don't support distinct in v7.0.3
>>
>> Ok I could use a temporary table but my select distinct tends to give
>> large results.
>>
>> Any clues?
>>
>> Dirk
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to
>> majordomo@postgresql.org
>>



_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________
------_=_NextPart_001_01C0B79F.E4C2D712--