CODEWITHSUNDEEP
sqlcountgroup-bydb2
Frederic Poirier
Frederic Poirier

Reputation: 37

DB2 group by month and count with multiple where clauses

I have the following DB2 table named INCIDENT

ticketid     Createdon     Severity
1            2012-01-01    1
2            2012-01-10    2
3            2012-01-15    2
4            2012-01-20    3
5            2012-01-29    3
6            2012-02-06    3
7            2012-02-15    3
8            2012-02-20    3
9            2012-02-20    4
10           2012-02-21    3

What i'm trying to get in terms of results:

Month      Sev1   Sev2   Sev3   Sev4
====================================
2012-01       1      2      2      0
2012-02       0      0      4      1

So far i have this:

select to_char(INCIDENT.CREATEDON, 'YYYY-MM') as month, 
count(case when severity = 1 then 1 else 0 end) as SEV1,
count(case when severity = 2 then 1 else 0 end) as SEV2
from INCIDENT group by to_char(INCIDENT.CREATEDON,'YYYY-MM')

but that gives me ALL the incidents in both columns...

Month      Sev1   Sev2   Sev3   Sev4
====================================
2012-01      11     11     11     11
2012-02      16     16     16     16

What concept am i not getting ? I would think this is doable but cannot wrap my head around this...

Upvotes: 0

Views: 1413

Answers (1)

Frederic Poirier
Frederic Poirier

Reputation: 37

I got it! of course i figured it out after posting my question....

select to_char(INCIDENT.CREATEDON, 'YYYY-MM') as month, 
sum(case when severity = 1 then 1 else 0 end) as sev1,
sum(case when severity = 2 then 1 else 0 end) as sev2,
sum(case when severity = 3 then 1 else 0 end) as sev3,
sum(case when severity = 4 then 1 else 0 end) as sev4
from INCIDENT 
where (createdon >=  current_timestamp - (minute(current_timestamp) minute) - (hour(current_timestamp) -1) hours - ( day(current_date) - 1 ) days - 13 month ) group by to_char(INCIDENT.CREATEDON,'YYYY-MM')

Upvotes: 1

Related Questions