我的新查询给了我一些错误:
错误: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/