SQL 报表统计 总计小计

Question

我是使用SQL SERVER2005 ,我有一张表
ID OUTPUT_STYLE AMOUNT DATE
1 电费 1000 2009-11-12
2 餐费 1000 2009-11-12
3 管理费 1000 2009-12-12
4 杂费 1000 2010-01-01
5 电费 1300 2009-11-12
6 餐费 1400 2009-11-12
7 管理费 2000 2009-12-12
8 杂费 1000 2010-01-01

我想得出一张报表是分月分类型统计的，如下
月份 餐费 管理费 杂费 电费 总计
1 计算每项当月的总费用，对应在下面
2
3
输出结果最后还有每一列相对应的统计

我这里已经有SQL语句，就是不知道怎么才能把相应列的的和输出量，请各位高手指教！只要做到我想要的结果，分数不会吝啬。
select (month(DATE)) as 月份,
sum(case when OUTPUT_STYTLE='电费' then AMOUNT else 0 end) as 电费,
sum(case when OUTPUT_STYTLE='管理费' then AMOUNT else 0 end) as 管理费,
sum(case when OUTPUT_STYTLE='杂费' then AMOUNT else 0 end) as 杂费,
sum(case when OUTPUT_STYTLE='餐费' then AMOUNT else 0 end) as 餐费,
sum(case when OUTPUT_STYTLE='其他' then AMOUNT else 0 end) as 其他,
sum(case when ID>0 then AMOUNT else 0 end ) as 总计
from DB_DAILY_OUTPUT where(year(DATE)='"+year+"') group by (month(DATE))

Answer 1

select 月份,电费,管理费,杂费,餐费,其他,总计 from
(select cast((month(DATE)) as char(2)) as 月份一,cast((month(DATE))as char(2)) as 月份,
sum(case when OUTPUT_STYTLE='电费' then AMOUNT else 0 end) as 电费,
sum(case when OUTPUT_STYTLE='管理费' then AMOUNT else 0 end) as 管理费,
sum(case when OUTPUT_STYTLE='杂费' then AMOUNT else 0 end) as 杂费,
sum(case when OUTPUT_STYTLE='餐费' then AMOUNT else 0 end) as 餐费,
sum(case when OUTPUT_STYTLE='其他' then AMOUNT else 0 end) as 其他,
sum(case when ID>0 then AMOUNT else 0 end ) as 总计
from DB_DAILY_OUTPUT where(year(DATE)='"+year+"') group by (month(DATE))
union
select '13' as 月份一,'合计' as 月份,
sum(case when OUTPUT_STYTLE='电费' then AMOUNT else 0 end) as 电费,
sum(case when OUTPUT_STYTLE='管理费' then AMOUNT else 0 end) as 管理费,
sum(case when OUTPUT_STYTLE='杂费' then AMOUNT else 0 end) as 杂费,
sum(case when OUTPUT_STYTLE='餐费' then AMOUNT else 0 end) as 餐费,
sum(case when OUTPUT_STYTLE='其他' then AMOUNT else 0 end) as 其他,
sum(case when ID>0 then AMOUNT else 0 end ) as 总计
from DB_DAILY_OUTPUT where(year(DATE)='"+year+"') )a
order by 月份一

Answer 2

好像要用个什么 交叉查询 。