RE: COUNT DISTINCT

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