select distinct count(id) vs select count(distinct id)

Question

I'm trying to get distinct values from a table. When I ran select distinct count(id) from table I got over a million counts. However if I ran select count(distinct id) from table I've got only around 300k counts. What was the difference of the two queries?

Thanks

Answer 1

When you do select distinct count(id) then you are basically doing:

select distinct cnt
from (select count(id) as cnt from t) t;

Because the inner query only returns one row, the distinct is not doing anything. The query counts the number of rows in the table (well, more accurately, the number of rows where id is not null).

On the other hand, when you do:

select count(distinct id)
from t;

Then the query counts the number of different values that id takes on in the table. This would appear to be what you want.