Tuesday, March 21, 2017

Count vs Count distinct in SQL

Had a new coder ask me how count works, vs count distinct. So I made a small example:

create table #tmp_example
(
       id int identity(1,1)
       , value varchar(255)
)

insert into #tmp_example
(
       value
)
select '1'
UNION select '2'
UNION select '3'
UNION select NULL


select
count(*)
, count(distinct value)
, count(distinct case when value = 1 then null else value end)
from #tmp_example


In this example it returns 4, 3, 2

4 because there are 4 rows (*) in the system.
3 because there are 3 distinct values where NULL is not counted a value in count distinct.
2 because there are 2 distinct values when 1 is cast as null during the counting process.