CODEWITHSUNDEEP
sqlsql-server
Tom Cruise
Tom Cruise

Reputation: 1415

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?

Upvotes: 0

Views: 9032

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93694

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 t.employeeid, 
       CASE 
         WHEN Count(m.employeeid) = 0 THEN 'No Manager' 
         ELSE Cast(Count(m.employeeid) 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

Upvotes: 2

JamieD77
JamieD77

Reputation: 13949

I think this is what you're wanting to do..

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

not sure why you would select m.managerid and count t.employeeid which would give an error since you're only grouping by t.employeeid

this might even perform better for you

SELECT  t.employeeid,
        CASE WHEN m.managercount = '0' THEN 'No Manager'
             ELSE m.managercount
        END
FROM    @employee t
        OUTER APPLY (SELECT CAST(COUNT(managerid) AS VARCHAR(10)) managercount
                     FROM   @employenrol m
                     WHERE  t.employeeid = m.employeeid
                    ) m

Upvotes: 0

jonju
jonju

Reputation: 2736

How about this

Select m.managerid,
    Case When count(t.employeeid) =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

Upvotes: 0

Related Questions