user3538475 - 1 year ago 47

SQL Question

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 Source

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
```