List grouped data by month including months in which there are no results

Question

Hello community The following query adds the occurrences of each activity state and then groups them by month

select
    date_format(date_created, '%b') month,
    month(date_created) pivot,
    sum(case when a.state = 'created' then 1 else 0 end) created,
    sum(case when a.state = 'notified' then 1 else 0 end) notified,
    sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
    sum(case when a.state = 'approved' then 1 else 0 end) approved,
    sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
    sum(case when a.state = 'attended' then 1 else 0 end) attended,
    sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
    count(a.id) as total
from activities a
group by 1 order by pivot desc;

So I get the following result

I would like to add to this result the months in which there is no data and auto-complete the sums of state with zeros

I share this sqlfiddle

I found this answer to a similar scenario but I can not understand how I could apply it to the case that I present to you

Thank you very much for your help

Lukasz Szozda · Accepted Answer · 2017-12-01T20:27:30.953

1

You could use date table(subquery) and LEFT JOIN:

select
    s.name month,
    s.m pivot,
    sum(case when a.state = 'created' then 1 else 0 end) created,
    sum(case when a.state = 'notified' then 1 else 0 end) notified,
    sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
    sum(case when a.state = 'approved' then 1 else 0 end) approved,
    sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
    sum(case when a.state = 'attended' then 1 else 0 end) attended,
    sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
    count(a.id) as total
from (SELECT  1 m, 'Jan' AS name 
      UNION SELECT 2, 'Feb' 
      UNION SELECT 3, 'Mar'
      UNION ...) s
LEFT JOIN activities a
  ON s.m = month(date_created)
  --AND s.y = year(date_created)   -- if needed 
group by 1
order by pivot desc;

DBFiddle Demo

edited Dec 01 '17 at 20:27

answered Dec 01 '17 at 20:11

Lukasz Szozda

162,964
23
234
275

1

Thank you very much. It's just the result I was looking for. – Mario Dec 01 '17 at 20:20
1

@user615274 Please note that if you have data from many years you need to add 3rd column with year to avoid aggregation data from 2 different years. – Lukasz Szozda Dec 01 '17 at 20:21
Could you develop your comment, please. – Mario Dec 01 '17 at 20:25
@user615274 For example you have data from `JAN 2017` and `JAN 2016`. If you use `month(created_date)` only then both `JAN` will be aggregated as one group. – Lukasz Szozda Dec 01 '17 at 20:26
I believe that this case is effectively addressed in the database, I avoid the aggregation message grouping by month and pivot `group by 1, 2`. Do you think that in long term is a correct solution? – Mario Dec 01 '17 at 20:37

List grouped data by month including months in which there are no results

1 Answers1

Linked