Thread: COUNT DISTINCT

COUNT DISTINCT

From

"Eric Jain"

Date:

15 July 2000, 08:29:28

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"

Any ideas how do I get this done properly?


--
Eric Jain

Re: COUNT DISTINCT

From

Andrew McMillan

Date:

15 July 2000, 08:51:24

Eric Jain wrote:
>
> 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"
>
> Any ideas how do I get this done properly?

SELECT count( DISTINCT(url,id) ) FROM log;

Seems to work for me...

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)


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

RE: COUNT DISTINCT

From

"Eric Jain"

Date:

15 July 2000, 09:43:05

> 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...


--
Eric Jain

Re: COUNT DISTINCT

From

Andrew McMillan

Date:

15 July 2000, 09:57:23

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

Re: COUNT DISTINCT

From

"Len Morgan"

Date:

15 July 2000, 13:31:31

I think what you are really looking for is a GROUPing:

SELECT url,id,count(*) FROM log GROUP BY url,id ;

The number of records returned is the NUMBER if different url/id pairs.  The
value of count(*) in each record is the count
for that particular pair.  Hope this helps

len morgan

RE: COUNT DISTINCT

From

"Eric Jain"

Date:

16 July 2000, 15:00:33

> > 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;

Good idea! Unfortunatly this ignores all rows with NULL in either of
the fields...

I guess if I'm a bit patient one day pgsql will allow for something
like:

SELECT COUNT(SELECT DISTINCT url,id FROM log) FROM log;


--
Eric Jain

RE: COUNT DISTINCT

From

Stephan Szabo

Date:

16 July 2000, 17:53:24

On Sun, 16 Jul 2000, Eric Jain wrote:

> > > 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;
>
> Good idea! Unfortunatly this ignores all rows with NULL in either of
> the fields...

Depending on what you want, you might be able to just get away with
using coalesce on each of them...

select count (distinct ( coalesce(text(advert_no), 'NULL') ||
coalesce(text(pool_id), 'NULL' ) ) ) from redirect;

You have to decide however what it means when one or both are null,
since count(field) ignores nulls.  Should it ignore rows when either
are null, both, never?  The above gives never, if you want both,
you can either use case to give you a null in that case or a where
to just ignore those rows.

The two column case might make a neat extension though, even if it
doesn't actually appear to be allowed by the sql92 draft I have...

> I guess if I'm a bit patient one day pgsql will allow for something
> like:
>
> SELECT COUNT(SELECT DISTINCT url,id FROM log) FROM log;

I don't think this would do what you want.  This would give a number
of rows equal to the number of rows in log all with the same value.
You probably want some variation on
select count(*) from (select distinct url,id from log);
which may show up eventually (maybe faster if you help ;) ).

RE: COUNT DISTINCT

From

"Eric Jain"

Date:

17 July 2000, 05:09:18

> Depending on what you want, you might be able to just get away with
> using coalesce on each of them...
>
> select count (distinct ( coalesce(text(advert_no), 'NULL') ||
> coalesce(text(pool_id), 'NULL' ) ) ) from redirect;

Great... This works perfectly. A bit of a monster though :-)


--
Eric Jain

Re: COUNT DISTINCT

From

Bruce Momjian

Date:

17 July 2000, 12:25:37

[ Charset ISO-8859-1 unsupported, converting... ]
> > Depending on what you want, you might be able to just get away with
> > using coalesce on each of them...
> >
> > select count (distinct ( coalesce(text(advert_no), 'NULL') ||
> > coalesce(text(pool_id), 'NULL' ) ) ) from redirect;
>
> Great... This works perfectly. A bit of a monster though :-)

Hey, we didn't design SQL, we just implement it.  :-)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: COUNT DISTINCT

From

Jeff Waugh

Date:

17 July 2000, 15:17:46

> Bruce Momjian wrote:
>
> Hey, we didn't design SQL, we just implement it.  :-)


Oh, that's quotable... ;)

- Jeff


-- jdub@aphid.net --------------------------------- http://linux.conf.au/ --

              linux.conf.au - coming to Sydney in January 2001

    Installing Linux Around Australia - http://linux.org.au/installfest/