我需要将A表中的几列求和放在B表中
A表
Date
value1
value2B表
Month
sum1
sum2我写成insert B (Month,sum1,sum2) select Date, sum(value1), sum(value2) where Date between ...肯定不对
这个日期应该咋办呢?
A表
Date
value1
value2B表
Month
sum1
sum2我写成insert B (Month,sum1,sum2) select Date, sum(value1), sum(value2) where Date between ...肯定不对
这个日期应该咋办呢?
-- 随手写的 试试看
INSERT INTO B
SELECT DATEPART(mm, [Date]) AS [Month], SUM(value1) AS sum1, SUM(value2) AS sum2
FROM A
GROUP BY DATEPART(mm, [Date])
还有你那个B表Month是什么东西,如果B表那个没什么用的话insert B (Month,sum1,sum2) select DISTINCT(这个地方看这个日期没整没白你要干什么,如果只要2011-5-1那这地方可以写成'2011-05-01' 如果用表的数据,可以CONVERT先转换了), sum(value1), sum(value2)
A表每天一条数据,B表每月求和。
SELECT month([Date]) AS [Month], SUM(value1) AS sum1, SUM(value2) AS sum2
FROM A
GROUP BY month([Date])
A表每天一条数据,B表每月求和。
select date,sum(),sum()
from table
group by date
select cast(DATEPART(yyyy, date) as varchar(4))+'-'+cast(DATEPART(mm, date) as varchar(2))+'-1',sum(va),sum(vb) from a group by cast(DATEPART(yyyy, date) as varchar(4))+'-'+cast(DATEPART(mm, date) as varchar(2))
类似这个算了你帮改好算了
insert B (Month,sum1,sum2) select cast(DATEPART(yyyy, date) as varchar(4))+'-'+cast(DATEPART(mm, date) as varchar(2))+'-1',sum(value1),sum(value2) from a group by cast(DATEPART(yyyy, date) as varchar(4))+'-'+cast(DATEPART(mm, date) as varchar(2))
这个可以实现你要的,求每月的合,把每月的第一天写入新表