Re: Counting unique rows as an aggregate.

Richard Broersma wrote:
> On Mon, Sep 29, 2008 at 4:36 PM, Tom Lane <tgl@sss.pgh.pa.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.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au