T-SQL Count rows with specific values (Multiple in one query)

Question

I need some help with a T-SQL query. I want to count fields that have a special value(e.g. >1).

Assuming i have a table like:

IGrp | Item | Value1 | Value2 ############################# A    | I11  | 0.52   | 1.18 A    | I12  | 1.30   | 0.54 A    | I21  | 0.49   | 2.37 B    | I22  | 2.16   | 1.12 B    | I31  | 1.50   | 0.28 

I want a result like:

IGrp | V1High | V2High  ###################### A    | 1      | 2 B    | 2      | 1 

In my mind this should be going with this expression

SELECT IGrp, COUNT(Value1>1) AS V1High, COUNT(Value2>1) AS V2High FROM Tbl GROUP BY IGrp 

But that's not possible in T-SQL since the Count() does not take boolean values. So is it really the only possible way to do multiple queries with WHERE Value>1 and COUNT(*) and join them afterwards? Or is there a trick to accomplish the desired result?

Thanks in advance.

Answer 1

SELECT IGrp,      COUNT(CASE WHEN Value1 > 1 THEN 1 ELSE NULL END) AS V1High,      COUNT(CASE WHEN Value2 > 1 THEN 1 ELSE NULL END) AS V2High  FROM Tbl GROUP BY IGrp 

Answer 2

You can use the CASE statement:

SELECT IGrp,      SUM(CASE WHEN Value1>1 THEN 1 ELSE 0 END) AS V1High,      SUM(CASE WHEN Value2>1 THEN 1 ELSE 0 END) AS V2High  FROM Tbl GROUP BY IGrp