user3538475 user3538475 - 7 months ago 9
SQL Question

How to use count inside case

Please help me to count using case statement.

I want this result:

Equal : 50
GT : 25
LT : 15


Below is my code:

select Input,
CASE
when math = '=' then
count(case when Input = UDTarget then Input else 0 end)
end as Equals,
CASE
when math = '>' then
count(case when ISNUMERIC(Input) < ISNUMERIC(UDTarget) then Input else 0 end)
end as GT,
CASE when math = '<' then
count(case when ISNUMERIC(Input) > ISNUMERIC(UDTarget) then Input else 0 end)
END as LT
FROM [NEWSEMAKPI].[dbo].[NewCriteria] NC
inner join [NEWSEMAKPI].[dbo].[UpdateData] UD ON UD.Cid = NC.Id
where InputWeek='15'
group by Input

Answer

your error shown because u didnt put math in your group by clause. so you should put it inside

select Input,
      CASE
      when math = '=' then 
        count(case when Input = UDTarget then Input else 0 end)
      end as Equals,
      CASE
      when math = '>' then 
        count(case when ISNUMERIC(Input) < ISNUMERIC(UDTarget) then Input else 0 end)
      end as GT,
      CASE when math = '<' then 
        count(case when ISNUMERIC(Input) > ISNUMERIC(UDTarget) then Input else 0 end)  
      END as LT 
FROM [NEWSEMAKPI].[dbo].[NewCriteria] NC 
inner join [NEWSEMAKPI].[dbo].[UpdateData] UD ON UD.Cid = NC.Id 
where InputWeek='15' 
group by Input, math 
Comments