SQL Server issue when grouping by year,month, day

Question

I have the below query where I get the past 6 rows from column 'FileSize' and total them into separate column called 'previous'. What I need is to group the results by year,month, day.

This is what I have:

SELECT DATEPART(DAY,CompleteTime )
  , SUM(ja.FileSize)
  , SUM(FileSize) OVER (ORDER BY DATEPART(DAY,CompleteTime ) ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) as previous
FROM Jobs_analytics ja
WHERE CompleteTime Between '2020-7-13 00:00:00' AND GETDATE()
GROUP BY DATEPART(DAY,CompleteTime )

However SQL wants me to add the FileSize to the group by clause. But When I do that I get every file in the results set. Since SUM(FileSize) OVER (ORDER BY DATEPART(DAY,CompleteTime ) ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) as previous was in a SUM function I didn't think I needed to include it in the group by clause?

Is there anyway I can group my results set by year,month, day?

Answer 1

The inner sum() takes care of the group aggregate. The outer sum() over () is the analytic function that looks over the prior rows (which are now grouped and summed themselves.)

SELECT
    CAST(CompleteTime AS DATE), SUM(FileSize) AS TotalSize,
    SUM(SUM(FileSize)) OVER (
        ORDER BY CAST(CompleteTime AS DATE)
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS Previous
FROM Jobs_analytics
WHERE CompleteTime BETWEEN '2020-07-13 00:00:00' AND GETDATE()
GROUP BY CAST(CompleteTime AS DATE);

Be careful with datepart(day, ...) as it's going to return a value from 1 to 31 and will collide with other months/years once you expand your date range enough to cover multiple dates falling on the same day of month.

Answer 2

It's expecting to sum the column FileSize when you want to sum the sum:

SUM(SUM(FileSize)) OVER (ORDER BY DATEPART(DAY,CompleteTime ) ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) as previous