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

Question

Hi.
I could 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 thats 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

You can put a CASE ... WHEN ... statement inside the COUNT() functions to return 1 when the conditions hold, 0 otherwise.

Answer 2

make use of case when will do work for you

SELECT IGrp, 
 count(case when isnull(Value1,0)>1 then 1 else 0 end) AS V1High, 
 count(case when isnull(Value2,0)>1 then 1 else 0 end) AS V2High 
FROM Tbl GROUP BY IGrp 

Answer 3

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 

Answer 4

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