Using count() .. Over() in mysql

Question

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

dbfiddle

score 1 · Accepted Answer · answered Aug 19 '20 at 10:52

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')

I added a ',' after requested_month but works just fine, Thank you — Bisoux, Aug 19 '20 at 10:56

score 1 · Answer 2 · answered Aug 19 '20 at 19:14

Since it is just for last 120 days (last years same month wouldnt occur) so we can also use distinct instead of group by), something like below:

    select distinct status, 
           count(*) over (partition by status) as total_by_status,
           date_format(requestedDate, '%b') mymonth,
           count(Month(requestedDate)) over (partition by Month(requestedDate)) as total_by_month,
           count(*) over () as total_by_month
    from reports
    where requestedDate between current_date - interval 120 day and current_date
order by status, mymonth

Demo

Using count(*) .. Over(*) in mysql

2 Answers2

Using count() .. Over() in mysql