RE: counting distinct rows on more than one column - Mailing list pgsql-sql

<p><font size="2">SELECT count(*) FROM (SELECT DISTINCT a, b FROM t) AS t2;</font><p><font size="2">should give you
whatyou want.</font><br /><p><font size="2">MikeA</font><br /><p><font size="2">>> -----Original
Message-----</font><br/><font size="2">>> From: Dirk Lutzebaeck [<a
href="mailto:lutzeb@aeccom.com">mailto:lutzeb@aeccom.com</a>]</font><br/><font size="2">>> Sent: 28 March 2001
16:11</font><br/><font size="2">>> To: pgsql-sql@postgresql.org</font><br /><font size="2">>> Subject:
[SQL]counting distinct rows on more than one column</font><br /><font size="2">>> </font><br /><font
size="2">>></font><br /><font size="2">>> </font><br /><font size="2">>> Hi,</font><br /><font
size="2">>></font><br /><font size="2">>> on 7.0.3 want to COUNT</font><br /><font size="2">>>
</font><br/><font size="2">>>   SELECT DISTINCT a,b FROM t;</font><br /><font size="2">>> </font><br
/><fontsize="2">>> I can't find a solution because any combination with</font><br /><font size="2">>> count
withmore than one column gives syntax errors.</font><br /><font size="2">>> </font><br /><font size="2">>>
Onesolution would be to set a view:</font><br /><font size="2">>> </font><br /><font size="2">>> CREATE
VIEWv AS SELECT DISTINCT a,b FROM t;</font><br /><font size="2">>> </font><br /><font size="2">>> and
then</font><br/><font size="2">>> </font><br /><font size="2">>> SELECT count(a) FROM v</font><br /><font
size="2">>></font><br /><font size="2">>> but views don't support distinct in v7.0.3</font><br /><font
size="2">>></font><br /><font size="2">>> Ok I could use a temporary table but my select distinct tends to
give</font><br/><font size="2">>> large results.</font><br /><font size="2">>> </font><br /><font
size="2">>>Any clues?</font><br /><font size="2">>> </font><br /><font size="2">>> Dirk</font><br
/><fontsize="2">>> </font><br /><font size="2">>> </font><br /><font size="2">>> </font><br /><font
size="2">>>---------------------------(end of </font><br /><font size="2">>>
broadcast)---------------------------</font><br/><font size="2">>> TIP 1: subscribe and unsubscribe commands go
to</font><br /><font size="2">>> majordomo@postgresql.org</font><br /><font size="2">>> </font><code><font
size="3"><br/><br /> _________________________________________________________________________<br /> This e-mail and
anyattachments are confidential and may also be privileged and/or copyright <br /> material of Intec Telecom Systems
PLC(or its affiliated companies). If you are not an <br /> intended or authorised recipient of this e-mail or have
receivedit in error, please delete <br /> it immediately and notify the sender by e-mail. In such a case, reading,
reproducing,<br /> printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. <br />
IntecTelecom Systems PLC. does not represent or warrant that an attachment hereto is free <br /> from computer viruses
orother defects. The opinions expressed in this e-mail and any <br /> attachments may be those of the author and are
notnecessarily those of Intec Telecom <br /> Systems PLC. <br /><br /> This footnote also confirms that this email
messagehas been swept by<br /> MIMEsweeper for the presence of computer viruses. <br />
__________________________________________________________________________<br/></font></code>