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.

LukeH · Accepted Answer · 2010-07-07 10:20:34Z

75

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

answered Jul 7, 2010 at 10:20

LukeH

271k59 gold badges373 silver badges411 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jing Over a year ago

Based on my experience SQL Server can work well with this command.

cjk · Accepted Answer · 2010-07-07 10:32:03Z

12

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

edited Jul 7, 2010 at 10:32

answered Jul 7, 2010 at 10:16

cjk

46.6k9 gold badges83 silver badges113 bronze badges

2 Comments

LukeH Over a year ago

This won't work. All non-null values are included in the count.

cjk Over a year ago

@HoseynHeydari no, check the edits, I corrected and the example is now correct. If an answer is wrong on SO, it is deleted.

Pranay Rana · Accepted Answer · 2011-05-30 04:50:59Z

3

make use of case when will do work for you

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

edited May 30, 2011 at 4:50

answered Jul 7, 2010 at 10:16

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

3 Comments

Marks Over a year ago

Thanks, somehow i didn't thought of this.

LukeH Over a year ago

This won't work. All non-null values are included in the count.

LukeH Over a year ago

That's not what I meant. Your CASE statement returns either 1 or 0, meaning that the COUNT is incremented in either case. Your CASE statement should return NULL when you don't want the count to be incremented.

Pascal Belloncle · Accepted Answer · 2013-03-02 00:24:15Z

2

SELECT IGrp, 
    COUNT(CASE WHEN Value1 = 'Foo' THEN 1 ELSE NULL END) AS Tot_Foo, 
    COUNT(CASE WHEN Value1 = 'Blah' THEN 1 ELSE NULL END) AS Tot_Blah 
FROM Tbl
GROUP BY IGrp

This can also be used to compare 2 different values for the same field, with minor changes as shown above.

Very helpful for verifying values that are supposed to exist in a 1:1 ratio.

edited Mar 2, 2013 at 0:24

Pascal Belloncle

11.4k3 gold badges59 silver badges56 bronze badges

answered Mar 2, 2013 at 0:05

user2125525

211 bronze badge

Comments

Community · Accepted Answer · 2012-04-11 19:50:30Z

0

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

edited Apr 11, 2012 at 19:50

CommunityBot

11 silver badge

answered Jul 7, 2010 at 10:16

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Comments

Brian Tompsett - 汤莱恩 · Accepted Answer · 2019-11-08 22:56:43Z

0

You can also use:

select
count(nullif(field > minvalue,false))

edited Nov 8, 2019 at 22:56

Brian Tompsett - 汤莱恩

5,92772 gold badges63 silver badges135 bronze badges

answered Nov 8, 2019 at 18:43

ryager

314 bronze badges

Collectives™ on Stack Overflow

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

6 Answers 6

1 Comment

2 Comments

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

2 Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related