|
|
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) – returns all Non-NULL values(including duplicates)
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(*) returns 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 the duplicates from count(Name) then use distinct keyword in it.
select COUNT(distinct Name) from #tempTable –-returns 3
|
|
|
|
|
|
|