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

GMB · Accepted Answer · 2020-08-19 10:52:05Z

1

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

answered Aug 19, 2020 at 10:52

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bisoux Over a year ago

I added a ',' after requested_month but works just fine, Thank you

Emaborsa Nov 6 at 6:47

Do you really need sum(count()) in MySql? Isn't count() enough?

Sourish · Accepted Answer · 2020-08-19 19:14:39Z

1

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

answered Aug 19, 2020 at 19:14

Sourish

262 bronze badges

Collectives™ on Stack Overflow

Using count() .. Over() in mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related