求按月份统计金额的sql语句

create table sale
(saleid int,
 sadate datetime,
 smoney int
)insert into sale
select 1,'2006-5-20',100
union all
select 2,'2006-5-26',200
union all
select 3,'2006-5-27',200
union all
select 4,'2006-5-28',500
union all
select 5,'2006-6-30',600
union all
select 6,'2006-6-1',4000
union all
select 7,'2005-6-1',3000select * from saleselect datepart(yyyy,dateadd(mm,aa,sadate))as 年份,
datepart(mm,dateadd(mm,aa,sadate))as 月份,
sum(smoney) as 总工资
from
(
select sadate,smoney,case when datepart(dd,sadate)<=26 then 0 else 1 end as AA
from sale
)a
group by datepart(yyyy,dateadd(mm,aa,sadate)),datepart(mm,dateadd(mm,aa,sadate))
order by datepart(yyyy,dateadd(mm,aa,sadate)),datepart(mm,dateadd(mm,aa,sadate))
drop table sale/*测试结果
saleid      sadate                                                 smoney      
----------- ------------------------------------------------------ ----------- 
1           2006-05-20 00:00:00.000                                100
2           2006-05-26 00:00:00.000                                200
3           2006-05-27 00:00:00.000                                200
4           2006-05-28 00:00:00.000                                500
5           2006-06-30 00:00:00.000                                600
6           2006-06-01 00:00:00.000                                4000
7           2005-06-01 00:00:00.000                                3000年份          月份          总工资         
----------- ----------- ----------- 
2005        6           3000
2006        5           300
2006        6           4700
2006        7           600
*/