SQL Server – Count NULL Inside CASE Statement

sql server

What is the best way to count NULL values inside case?

Using a joined table – I am trying to count the values of the column P.ID (from the joined table), and to categorize the counted values into a new column via case.

For example:

  • when count 0 – display 'none'
  • when count 1-2 – display 'few'.
  • when count 3-5 – display 'medium
  • when count 5+ – display 'many'

However, since some of the values received from the join are null – I cannot count them.

What changes should I do to my query in order to refer every NULL as 0? 

SELECT 
    U.Mail, 
    COUNT(P.ID) AS PostCount, 
    CASE
        WHEN COUNT(P.ID) = 0 THEN'none' --doesn't work
        WHEN COUNT(P.ID) <= 2 THEN 'few'
        WHEN COUNT(P.ID) <= 5 THEN 'medium'
        ELSE 'many'
    END AS PostCountCategory
FROM
    Users U 
LEFT JOIN 
    Tag T ON U.Mail = T.Mail
LEFT JOIN 
    Post P on T.IDPost = P.ID
GROUP BY U.Mail;

Best Answer

I am not very clear on your question however you can alter your code as below for your null question:

select U.Mail, count(isnull(P.ID, 0)) as PostCount, 
    case 
        when count(P.ID) = 0 then 'none' --doesn't work
        when (count(P.ID) <= 2 and count(P.ID) > 0) then 'few'
        when (count(P.ID) <= 5 and count(P.ID) > 2 )then 'medium'
        else 'many'
    end PostCountCategory
from Users U 
left join Tag T on U.Mail = T.Mail
left join Post P on T.IDPost = P.ID
group by U.Mail

Alternatively you can use count(isnull(P.ID, 0)) inside your case statement. Hope this helps.

As suggested by Mustaccio, you may use coalesce also instead of isnull.

Related Question