Difference between count(*) and count(column_name)

We might assume that count(*) and count(column_name) will return same result count. But NO, in case of column holds any null values.

Count (*) – returns all values (including nulls and duplicates)

Count (Column_Name) – return all Non-NULL values

In the below script we will see how it works. So that it will be easy for us to understand.

create table #tempTable(Name char(1)) insert into #tempTable values('A') insert into #tempTable values('B') insert into #tempTable values(Null) insert into #tempTable values('C') insert into #tempTable values(Null) insert into #tempTable values('C') select COUNT(*) from #tempTable select COUNT(Name) from #tempTable drop table #tempTable

Output: 6 and 4

The table #temptable has total 6 rows. Count(*) retruns all the rows including null/duplicates but where as count(name) returns only 4 rows which includes duplicates(‘C’) but not null values.

If you want to remove duplicates from count(Name) use COUNT(distinct Name).

select COUNT(distinct Name) from #tempTable –returns 3.