How can I Sum for each month 4 month back including itself? (SQL)-CodePudding

Im braking my head to solve this problem, I hope you guys. Can help me. So I have this Table:

Date	Total
2021/01/01	10000
2021/02/01	20000
2021/03/01	30000
2021/04/01	30000
2021/05/01	10000
2021/06/01	20000
2021/07/01	30000
2021/08/01	30000.
2021/09/01	10000
2021/010/01	20000
2021/011/01	30000
2021/12/01	30000.

and I need to calculate for each month the sum of 4 month before total (including itself). Thanks for the help

CodePudding user response：

You can use windowed/analytic functions.

SUM(total) OVER (ORDER BY date
             ROWS BETWEEN 3 PRECEDING
                      AND CURRENT_ROW
           )
              AS total_4months

CodePudding user response：

SELECT Date, SUM(Total) OVER (ORDER BY Date RANGE INTERVAL '4' MONTH
PRECEDING) FROM tbl;