Case and Null

[Mdavis123]

I'm using this select statement, how do I account for nulls?

select SUM(CASE c.Gender WHEN 'Male' THEN 1 ELSE 0 END) AS Male,
SUM(CASE c.Gender WHEN 'Female' THEN 1 ELSE 0 END) AS Female,
SUM(CASE c.Gender WHEN 'Unknown' THEN 1 ELSE 0 END) AS Unknown,
SUM(CASE c.Gender WHEN null THEN 1 ELSE 0 END) AS nulls

From MyTable
Nulls column always shows 0

Tia
Mike D

 

[SQLBill]

Try ISNULL. The syntax is ISNULL(check_expression, replacement_expression). Check out Books Online.

-SQLBill

 

[Mdavis123]

SQLbill,
This worked after I swapped the Then and Else values.

SUM(CASE c.Gender WHEN isnull(c.Gender,'T') THEN 0 ELSE 1 END) AS nulls

Not sure why,
Thanks SQLBill,
MikeD