按月列出分组数据，包括没有结果的月份 [英] List grouped data by month including months in which there are no results

问题描述

Hello community 以下查询添加每个活动状态的出现次数，然后按月分组

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;

所以我得到以下结果

我想将没有数据的月份添加到此结果中，并用零自动完成状态总和

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

我分享这个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

非常感谢您的帮助

推荐答案

你可以使用日期表(子查询)和LEFT JOIN:

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 演示

这篇关于按月列出分组数据，包括没有结果的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！