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.
-
check out my answer. I'm leaving a comment because they seem to send out alerts to stackoverflow users quicker.Eric– Eric2009-06-19 17:40:31 +00:00Commented Jun 19, 2009 at 17:40
Add a comment
|
4 Answers
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
Comments
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.
Comments
select count(foo)
from t1
where a = 1
union
select count(foo)
from t1
where b = 2
....
Comments
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
