Bill the Lizard Bill the Lizard - 7 months ago 17
SQL Question

In SQL, what's the difference between count(column) and count(*)?

I have the following query:

select column_name, count(column_name)
from table
group by column_name
having count(column_name) > 1;


What would be the difference if I replaced all calls to
count(column_name)
to
count(*)
?

This question was inspired by How do I find duplicate values in a table in Oracle?.




To clarify the accepted answer (and maybe my question), replacing
count(column_name)
with
count(*)
would return an extra row in the result that contains a
null
and the count of
null
values in the column.

Answer

count(*) counts NULLs and count(column) does not

[edit] added this code so that people can run it

create table #bla(id int,id2 int)
insert #bla values(null,null)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,null)

select count(*),count(id),count(id2)
from #bla

results 7 3 2

Comments