COUNT() doesn't work with GROUP BY?

Question

SELECT COUNT(*) FROM table GROUP BY column

I get the total number of rows from table, not the number of rows after GROUP BY. Why?

Answer 1

Because that is how group by works. It returns one row for each identified group of rows in the source data. In this case, it will give the count for each of those groups.

To get what you want:

select count(distinct column)
from table;

EDIT:

As a slight note, if column can be NULL, then the real equivalent is:

select (count(distinct column) +
        max(case when column is null then 1 else 0 end)
       )
from table;