Re: count distinct

FlameDance wrote:

> Hi everyone,
>
> I need to know the number of distinct values of 4 columns of the same
> table.
> For one column I'd use this statement:
>
> select count (*) from (select distinct column1 from mytable);
>
> This requires a full table scan. For 4 columns I could issue this
> command 4 times, using a different column each time. That would amount
> to 4 time consuming full table scans.
>
> select count (*) from (select distinct column1 from mytable);
> select count (*) from (select distinct column2 from mytable);
> select count (*) from (select distinct column3 from mytable);
> select count (*) from (select distinct column4 from mytable);
>
> Is there a way to do this faster, ideally with just one full table scan?
>
> Stephan

SELECT COUNT(*)
FROM (
   SELECT DISTINCT column1, column2, column3, column4    FROM mytable);

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)