CODEWITHSUNDEEP
sqldb2
David Hamilton
David Hamilton

Reputation:

SQL (DB2) Return multiple conditional counts in a single query

I am trying select multiple conditional summaries into a single table result on a DB2 based database.

Example:

SELECT COUNT(FOO) FROM T1 WHERE T1.A=1 AS A_COUNT,
SELECT COUNT(FOO) FROM T1 WHERE T1.B=2 AS B_COUNT
Ext...

Any help is appreciated.

Upvotes: 8

Views: 20922

Answers (4)

Eric
Eric

Reputation: 8078

This will do it. 

SELECT  A_COUNT as Type ,COUNT(FOO) FROM T1 WHERE T1.A=1, 

Union


SELECT B_COUNT as Type, COUNT(FOO) FROM T1 WHERE T1.B=2

Upvotes: 0

tekBlues
tekBlues

Reputation: 5793

This will count the occurrence of each condition:

select sum(case when t1.a = 1 then 1 else 0 end) as A_COUNT
     , sum(case when t1.b = 2 then 1 else 0 end) as B_COUNT
  from t1
 where t1.a = 1
    or t1.b = 2

Upvotes: 8

l0b0
l0b0

Reputation: 58768

select count(foo)
  from t1
 where a = 1
union
select count(foo)
  from t1
 where b = 2
....

Upvotes: 1

Noah Yetter
Noah Yetter

Reputation: 396

select count(case when t1.a = 1 then foo else null end) as A_COUNT
     , count(case when t1.b = 2 then foo else null end) as B_COUNT
  from t1
 where t1.a = 1
    or t1.b = 2

Where clause is optional strictly speaking but may assist performance. Also "else null" is implicit when the else clause is omitted so you can safely leave that off as well.

Upvotes: 6

Related Questions