CODEWITHSUNDEEP
sqlsql-server
Dass
Dass

Reputation: 83

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

Upvotes: 0

Views: 822

Answers (1)

Mark
Mark

Reputation: 2063

Your error is shown because you didn't 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

Upvotes: 1

Related Questions