SQL (DB2) Returns multiple conditional counters in a single query

Question

I am trying to select multiple conditional summaries into a single table result in a DB2 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.

Answer 1

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 

Answer 2

 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 

If the sentence is not necessarily strict, but can help in productivity. Furthermore, "else null" is implicit when the else clause is omitted so you can safely leave it as well.

Answer 3

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

Answer 4

It 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