COUNT DISTINCT

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

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

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

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

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

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

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

[ 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

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/