How to aggregate a column in SQL for one week?-CodePudding

I have a table like this:

| date | value |
| -------- | -------------- |
| 1/1    | 1            |
| 1/1   | 2            |
| 1/2   | 2            |
| 1/3   | 2            |
| 1/3   | 3            |
| 1/4   | 5           |
| 1/4   | 2            |
| 1/5   | 2            |
| 1/5   | 3            |
| 1/5   | 4            |
| 1/6   | 2            |
| 1/7   | 4            |
| 1/7   | 5            |
| 1/7   | 2            |
| 1/7   | 1            |
| 1/7   | 3            |
| 1/8   | 4            |
| 1/9   | 2            |

I want to write a query to sum the values for the past one week for each day. my desired output is:

 | date | value |
| -------- | -------------- |
| 1/1    | 2 1            |
| 1/2   | 2 2 1            |
| 1/3   | 3 2 2 2 1            |
| 1/4   | 2 5 3 2 2 2 1           |
| 1/5   | 4 3 2 2 5 3 2 2 2 1             |
| 1/6   | 2 4 3 2 2 5 3 2 2 2 1            |
| 1/7   | 3 1 2 5 4 2 4 3 2 2 5 3 2 2 2 1           |
| 1/8   | 4 3 1 2 5 4 2 4 3 2 2 5 3 2 2            |
| 1/9   | 2 4 3 1 2 5 4 2 4 3 2 2 5 3 2            |

I want to aggregate every week. if I do single group by I only get sum for every day not for the past week.

SELECT date,SUM(value) AS total FROM table GROUP BY date

CodePudding user response：

Sounds like you want a accumulative sum over days of the week, but you have multiple days so those need to be rolled up first, grouped by and then have the sum applied over the dates. Try this and let me know if it works. I don't see a DB specified so I wrote it in T-SQL, SSMS.

I casted date because in my database engine the datetime was showing 00:00:00 after the date.

SELECT [DATE]
,SUM(SumValue) OVER (ORDER BY [DATE]) AS RunningTotal
FROM
(
SELECT CAST([DATE] AS DATE) AS [DATE]
      ,SUM([Value]) AS SumValue
  FROM [ValueTable]
  GROUP BY CAST([DATE] AS DATE)
) AS X

CodePudding user response：

You may use OVER clause

SELECT tot.inputDate, SUM(tot.inputVal)OVER(partition by '' ORDER BY inputDate ROWS BETWEEN 6 preceding and current row)  sum_7_days
FROM 
(SELECT inputDate, SUM(inputVal) as inputVal
FROM test
GROUP BY inputDate) as tot

The word preceding and current row will get number of rows before current row.

Please check this db fiddle