Re: COUNT DISTINCT

Eric Jain wrote:
>
> > SELECT count( DISTINCT(url,id) ) FROM log;
> >
> > Seems to work for me...
>
> Thanks... however:
>
> SELECT count( DISTINCT(url,id) ) FROM log;
> ERROR:  parser: parse error at or near ")"
>
> SELECT count( DISTINCT url,id ) FROM log;
> ERROR:  Aggregate functions may only have one parameter
>
> SELECT count( DISTINCT ON (url,id) ) FROM log;
> ERROR:  parser: parse error at or near "on"
>
> ...
>
> > Well, actually:
> > advert=# select count( distinct ( redirect_from ) ) from redirect;
> >  count
> > -------
> >   1980
> > (1 row)
> >
> > rather than:
> >
> > advert=# select count( redirect_from ) from redirect;
> >  count
> > -------
> >   4211
> > (1 row)
>
> SELECT count(DISTINCT url) FROM log; does work. The problem is to
> extend the DISTINCT over several fields...

Right - I should have looked closer shouldn't I :-)

What about...

advert=# select count( distinct ( (text(advert_no) || text(pool_id)) ) )
from redirect;
 count
-------
    38
(1 row)

Cheers,
                    Andrew.

--
_____________________________________________________________________
            Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267