CODEWITHSUNDEEP
sqlsqlitecountgroup-by
thelolcat
thelolcat

Reputation: 11615

COUNT() doesn't work with GROUP BY?

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?

Upvotes: 3

Views: 5099

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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;

Upvotes: 13

Brian
Brian

Reputation: 3914

Try this:

SELECT COUNT(*), column
FROM table
GROUP BY column

Upvotes: 2

Related Questions