Tom Cruise Tom Cruise - 4 months ago 8
SQL Question

count in case statement SQL

I have the below query in my sql program

SELECT
m.managerid,
CASE
WHEN (ISNULL(COUNT(t.employeeid), 0) = 0) THEN 'No Manager'
ELSE COUNT(m.managerid)
END
FROM employee t
LEFT JOIN employenrol m
ON t.employeeid = m.employeeid
GROUP BY t.employeeid


if count(t.employeeid)= 0
I need to display no manager else the actual count.


Conversion failed when converting the varchar value 'No Manager' to
data type int. Warning: Null value is eliminated by an aggregate or
other SET operation.


How to fix this?

Answer

From MSDN

The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

You need to do explicit conversion in else part since the first then statement returns VARCHAR which cannot be converted to INT.

SELECT m.managerid, 
       CASE 
         WHEN Count(t.employeeid) = 0 THEN 'No Manager' 
         ELSE Cast(Count(m.managerid) AS VARCHAR(50)) 
       END 
FROM   employee t 
       LEFT JOIN employenrol m 
              ON t.employeeid = m.employeeid 
GROUP  BY t.employeeid 

Also Count aggregate returns 0 when there you have NULL. No need to use ISNULL function