请教日期分段SUM怎么写

skyfly

80        5        2        30        2016-12-02 00:00:00
114        11        4        58        2016-12-03 00:00:00
85        8        1        38        2016-12-04 00:00:00
82        11        1        31        2016-12-05 00:00:00
83        9        0        39        2016-12-06 00:00:00
91        7        2        42        2016-12-07 00:00:00

SELECT SDate,sum(SaleGroup) as SaleGroup,sum(SzG) as SzG FROM dbo.Saleday Dbo_saleday
WHERE SDate>='01.12.2016' and SDate<='01.02.2017'
GROUP BY  SDate
order by SDate
上面是原SQL
我想让数据每5天为一行统计如何写

hhuaixiaozi

SELECT SDate = MIN(SDate) ,sum(SaleGroup) as SaleGroup,sum(SzG) as SzG FROM dbo.Saleday Dbo_saleday
WHERE SDate>='01.12.2016' and SDate<='01.02.2017'
GROUP BY  DATEDIFF(Day, '01.12.2016', SDate) / 5
order by SDate

skyfly

2016-12-01 00:00:00194        16       
2016-12-03 00:00:00423        45       
2016-12-08 00:00:00492        57       
2016-12-13 00:00:00171        32       
2017-01-01 00:00:0027        2       
2017-01-02 00:00:000        0       

返回数据不对..不是按5天返回...

kxiaper

SELECT SDateFrom = MIN(SDate) ,SDateTo = MAX(SDate) ,sum(SaleGroup) as SaleGroup,sum(SzG) as SzG FROM dbo.Saleday Dbo_saleday
WHERE SDate>='01.12.2016' and SDate<='01.02.2017'
GROUP BY  DATEDIFF(Day, '01.12.2016', SDate) / 5

skyfly

2016-12-01 00:00:00194        16       
2016-12-03 00:00:00423        45            ?怎么才3天       
2016-12-08 00:00:00492        57       
2016-12-13 00:00:00171        32       
2017-01-01 00:00:0027        2       
2017-01-02 00:00:000        0       

返回数据不对..不是按5天返回...
SDate = MIN(SDate)
这个不理解...为什么用MIN

kxiaper

日期会不连续？？？

kxiaper

是5天一行还是5条记录一行？

haigerrual

SDate = MIN(SDate)
这个不理解...为什么用MIN
--------
是不是sdate中按五天的计算，然后老大求得是五天中最小的一个（不然会出错的）

skyfly

按5天统计  不是5行记录统计

fanfan1980

--try

declare @dt1 datetime, @dt2 datetime
select @dt1='2016-12-01', @dt2='2017-02-01'
declare @T table([date] datetime)
while @dt1<=@dt2
begin
        insert @T select @dt1
        set @dt1=@dt1+5
end

SELECT A.[date] as SDate, isnull(sum(SaleGroup), 0) as SaleGroup, isnull(sum(SzG), 0) as SzG
FROM @T as A
left join dbo.Saleday Dbo_saleday as B on B.SDate between A.[date] and A.[date]+5
WHERE B.SDate between @dt1 and @dt2
GROUP BY  A.[date]