How to aggregate a column in SQL for one week?

Question

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

Answer 1

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