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.