Wednesday, July 16, 2014

[MySQL] Grouping Intervals and counting columns

Say you have table like this...

idcodedateactivity
12002014/06/04
22002014/05/04
35002014/04/04
45042014/05/28

...and now you want to get the past 7 days but count how many times 200 (success code) comes up and how many times a number other than 200 (failure code) comes up. So the idea is simple. Get total of success + failure codes, left outer join for success, left outer join for failure. The filter where the date is more the the current day minus 7 days. At the end, you should return a row that has the day, total count, success count, and failure count. the Got it? Yes you do. You're a trooper. Here is a sample:

select STR_TO_DATE(a.period, '%m-%d-%y') as period, ifnull(a.cnt,0) total, ifnull(b.cnt,0) success, ifnull(c.cnt,0) failure 
from
(
select
date_format(dateactivity , '%m-%d-%y') period,
count(*) cnt
from mytable
where dateactivity >= DATE_ADD(NOW(), INTERVAL -7 DAY)
group by date_format(dateactivity , '%m-%d-%y')
) a 
left outer join
(
select
date_format(dateactivity , '%m-%d-%y') period,
count(*) cnt
from mytable
where code = '200'
and dateactivity >= DATE_ADD(NOW(), INTERVAL -7 DAY)
group by date_format(dateactivity , '%m-%d-%y')
) b on a.period = b.period 
left outer join
(
select
date_format(dateactivity , '%m-%d-%y') period,
count(*) cnt
from mytable
where (code != '200' or code is null)
and dateactivity >= DATE_ADD(NOW(), INTERVAL -7 DAY)
group by date_format(dateactivity , '%m-%d-%y')
) c on a.period = c.period
;

I know that there are probably different ways of doing this. If you know how, please share. Sharing is caring. And I care for all.

No comments:

Post a Comment