Skip site navigation (1) Skip section navigation (2)

Re: Counting unique rows as an aggregate.

From: Klint Gore <kgore4(at)une(dot)edu(dot)au>
To: Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, r_musta <zepolen(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Counting unique rows as an aggregate.
Date: 2008-09-30 05:42:54
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Richard Broersma wrote:
> On Mon, Sep 29, 2008 at 4:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > SELECT count(distinct make), count(distinct color) from table WHERE >criteria<;
> Is this in the SQL spec?  I didn't know Agg functions could do this?

Yes. SQL92 6.5

  <set function specification> ::=
                COUNT <left paren> <asterisk> <right paren>
              | <general set function>

         <general set function> ::=
                <set function type>
                    <left paren> [ <set quantifier> ] <value expression> <right paren>

         <set function type> ::=
              AVG | MAX | MIN | SUM | COUNT

         <set quantifier> ::= DISTINCT | ALL

I never realised that you could use it for more than count though.  In 
8.3.3, it worked for sum/avg/stddev/variance. 


Klint Gore
Database Manager
Sheep CRC
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266
EMail: kgore4(at)une(dot)edu(dot)au

In response to

pgsql-general by date

Next:From: Craig RingerDate: 2008-09-30 05:49:27
Subject: ODBC driver crash
Previous:From: Richard BroersmaDate: 2008-09-30 05:04:58
Subject: Re: Counting unique rows as an aggregate.

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group