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

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

sqlpostgresql


Solution 1:[1]

You can use windowed/analytic functions.

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

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MatBailie
Solution 2 Saeed Esmaeelinejad

Related Questions