貌似这样 select 营业额 ,sum(营业额) from table group by charindex(convert(varchar(7),时间,23),时间) 回来再看看
经测试 SELECT DATEADD(mm, DATEDIFF(mm,0,salesTime), 0),SUM(sales) FROM A GROUP BY DATEADD(mm, DATEDIFF(mm,0,salesTime), 0)
请问MM是啥?我一到十二月份都统计,MM就是一到十二???
select datepart(month,salesTime),sum(sales) from a group datepart(month,salesTime) order by datepart(month,salesTime)
select datepart(month,salesTime),sum(sales) from a where datediff(year,salesTime,getdate())=0 group by datepart(month,salesTime) order by datepart(month,salesTime)
统计时应该得把同一年的数据过滤出来。 还有如果数据表的时间有不包括所有月份的情况 --> Test Data: @T declare @T table ([sales] int,[salestime] datetime) insert into @T select 10,'2009-1-1' union all select 12,'2009-2-10' union all select 55,'2009-3-1' union all select 33,'2009-4-1' union all select 23,'2009-4-1' union all select 4,'2009-5-1' union all select 78,'2009-7-8' union all select 1,'2009-8-1' union all select 11,'2009-9-1' union all select 34,'2009-10-12'--select * from @T --Code select 月份=b.tmonth,营业额=isnull(sum(sales),0) from @T a right join ( select '01' as tmonth union all select '02' union all select '03' union all select '04' union all select '05' union all select '06' union all select '07' union all select '08' union all select '09' union all select '10' union all select '11' union all select '12' ) b --月份临时表 on MONTH(a.salestime)=b.tmonth group by b.tmonth --Drop--Result /* 月份 营业额 ---- ----------- 01 10 02 12 03 55 04 56 05 4 06 0 07 78 08 1 09 11 10 34 11 0 12 0 */
参考:http://blog.csdn.net/wxg22526451/archive/2008/09/13/2924149.aspx
select 营业额 ,sum(营业额) from table group by charindex(convert(varchar(7),时间,23),时间) 回来再看看
SELECT DATEADD(mm, DATEDIFF(mm,0,salesTime), 0),SUM(sales)
FROM A
GROUP BY DATEADD(mm, DATEDIFF(mm,0,salesTime), 0)
请问MM是啥?我一到十二月份都统计,MM就是一到十二???
select datepart(month,salesTime),sum(sales) from a group datepart(month,salesTime) order by datepart(month,salesTime)
select datepart(month,salesTime),sum(sales) from a where datediff(year,salesTime,getdate())=0 group by datepart(month,salesTime) order by datepart(month,salesTime)
还有如果数据表的时间有不包括所有月份的情况
--> Test Data: @T
declare @T table ([sales] int,[salestime] datetime)
insert into @T
select 10,'2009-1-1' union all
select 12,'2009-2-10' union all
select 55,'2009-3-1' union all
select 33,'2009-4-1' union all
select 23,'2009-4-1' union all
select 4,'2009-5-1' union all
select 78,'2009-7-8' union all
select 1,'2009-8-1' union all
select 11,'2009-9-1' union all
select 34,'2009-10-12'--select * from @T
--Code
select 月份=b.tmonth,营业额=isnull(sum(sales),0)
from @T a right join
(
select '01' as tmonth union all select '02' union all select '03' union all
select '04' union all select '05' union all select '06' union all
select '07' union all select '08' union all select '09' union all
select '10' union all select '11' union all select '12'
) b --月份临时表
on MONTH(a.salestime)=b.tmonth
group by b.tmonth
--Drop--Result
/*
月份 营业额
---- -----------
01 10
02 12
03 55
04 56
05 4
06 0
07 78
08 1
09 11
10 34
11 0
12 0
*/
select datename(mm,时间) as 月份,sum(营业额) as 营业总额 from tb group by datename(mm,时间)