Ask how to write the date segment 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
The above is the original SQL
I want the data to be a row of statistics every 5 days. How to write

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:00 171 32
2017-01-01 00:00:0027 2
2017-01-02 00:00:000 0

The returned data is incorrect.. It is not returned in 5 days...

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? How can it be 3 days?
2016-12-08 00:00:00492 57
2016-12-13 00:00:00 171 32
2017-01-01 00:00:0027 2
2017-01-02 00:00:000 0

The returned data is incorrect.. It is not returned in 5 days...
SDate = MIN(SDate)
I don't understand this... why use MIN

kxiaper

Will the dates be discontinuous? ? ?

kxiaper

Is it a row for 5 days or a row for 5 records?

haigerrual

SDate = MIN(SDate)
I don't understand this... why use MIN
--------
Is it calculated based on five days in sdate, and then the boss finds the smallest one in five days (otherwise it will go wrong)

skyfly

Statistics based on 5 days, not 5 rows of records

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]