My data looks like the following,
requestedDate Status
2020-04-21 APPROVED
2020-04-23 APPROVED
2020-04-27 PENDING
2020-05-21 PENDING
2020-06-01 APPROVED
I would like to extarct a report that looks like the following where the count is by status and month.
Status StatusCount Month MonthCount CountTotal
APPROVED 2 APR 3 5
PENDING 1 MAY 1 5
APPROVED 1 JUN 1 5
My sql looks like the following,
select distinct
status,
count(status) over (partition by status) as total_by_status,
CASE
WHEN Month(requestedDate) = 1 THEN 'JAN'
WHEN Month(requestedDate) = 2 THEN 'FEB'
WHEN Month(requestedDate) = 3 THEN 'MAR'
WHEN Month(requestedDate) = 4 THEN 'APR'
WHEN Month(requestedDate) = 5 THEN 'MAY'
WHEN Month(requestedDate) = 6 THEN 'JUN'
WHEN Month(requestedDate) = 7 THEN 'JUL'
WHEN Month(requestedDate) = 8 THEN 'AUG'
WHEN Month(requestedDate) = 9 THEN 'SEP'
WHEN Month(requestedDate) = 10 THEN 'OCT'
WHEN Month(requestedDate) = 11 THEN 'NOV'
WHEN Month(requestedDate) = 12 THEN 'DEC'
END AS myMONTH,
count(Month(requestedDate)) over (partition by Month(requestedDate)) as total_by_month,
count(*) over () as Totals
from Reports
where
requestedDate between DATE_SUB(CURDATE(), INTERVAL 120 DAY) and date(CURDATE())
order by 1;
The output for that looks like,
status total_by_status myMONTH total_by_month Totals
APPROVED 3 APR 3 5
APPROVED 3 JUN 1 5
PENDING 2 APR 3 5
PENDING 2 MAY 1 5
First you need a valid aggregation query. Then you can use window functions on top of it (here, you would typically compute window sums of the counts).
I would write this as:
select
status,
count(*) status_count,
date_format(requestedDate, '%b') requested_month
sum(count(*)) over(partition by year(requestedDate), month(requestedDate)) month_count,
sum(count(*)) over() total_count
from reports
where requestedDate between current_date - interval 120 day and current_date
group by status, year(requestedDate), month(requestedDate), date_format(requestedDate, '%b')