mysql - SQL按月份然后按总数按“文件夹名称”分组

我的新查询给了我一些错误：

错误：ORA-0093：SQL命令未正确结束。

    select coalesce(a.group_name, 'Total') as group_name,
    sum(case when month (a.sent_date)=1 then a.total_sent else 0 end) as January,
    sum(case when month(a.sent_date)=2 then a.total_sent else 0 end) as February,
    sum(case when month(a.sent_date)=3 then a.total_sent else 0 end) as March,
    sum(case when month(a.sent_date)=4 then a.total_sent else 0 end) as April,
    sum(case when month(a.sent_date)=5 then a.total_sent else 0 end) as May,
    sum(case when month(a.sent_date)=6 then a.total_sent else 0 end) as June,
    sum(case when month(a.sent_date)=7 then a.total_sent else 0 end) as July,
    sum(case when month(a.sent_date)=8 then a.total_sent else 0 end) as August,
    sum(case when month(a.sent_date)=9 then a.total_sent else 0 end) as September,
    sum(case when month(a.sent_date)=10 then a.total_sent else 0 end) as October,
    sum(case when month(a.sent_date)=11 then a.total_sent else 0 end) as November,
    sum(case when month(a.sent_date)=12 then a.total_sent else 0 end) as December
    from c_group a
    where a.partner_id=123 AND
    a.sent_date >= '01-JAN-2012'
    and a.sent_date <= '31-DEC-2012'
    group by a.group_name with rollup;

=========

这是我第一次在这里发帖，也是查询的初学者。

我正在运行一个查询，整天返回各种文件夹名称。我想按文件夹名称分组，并按月对每个文件夹名称进行总计，然后在底部对每一列进行总计。这是我正在运行的查询：

select a.group_name, a.sent_date, a.total_sent
from
c_group a
where
a.partner_id=123
and a.sent_date >= '01-JAN-2012'
and a.sent_date <= '31-DEC-2012'

显示如下：

GROUP_NAME  SENT_DATE   TOTAL_SENT
Group A     1-Jan-12    37
Group B     3-Jan-12    25
Group C     1-May-12    10
Group D     1-May-12    8
Group D     1-Jan-12    11
Group A     1-Dec-12    9

我需要结果显示为：

           January  February    March   April   May June    July    August  September   October November    December
Group A
Group B
Group C
Group D
...
....
...
....
Total       Sum of above    Sum of above    Sum of above    Sum of above    Sum of above    Sum of above    Sum of above    Sum of above    Sum of above    Sum of above    Sum of above    Sum of above

最佳答案

您想将条件聚合与rollup结合使用：

select coalesce(a.group_name, 'Total') as group_name,
       sum(case when month(a.sent_date) = 1 then a.total_sent else 0 end) as January,
       sum(case when month(a.sent_date) = 2 then a.total_sent else 0 end) as February,
       . . .
       sum(case when month(a.sent_date) = 12 then a.total_sent else 0 end) as December
from c_group a
where a.partner_id = 123 AND
      a.sent_date >= '01-JAN-2012' AND
      a.sent_date <= '31-DEC-2012'
group by a.group_name with rollup;

关于mysql - SQL按月份然后按总数按“文件夹名称”分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25874189/