postgresql - cumulative sum group by type and week (multiple columns)-CodePudding

I want to have a cumulative sum but my condition needs to group by multiple columns

table: customer

type	week	id
A	2022-01	abc123
B	2022-01	bcd123
B	2022-02	efg123
A	2022-02	klc123
B	2022-02	mad123

My query now:

SELECT   week, type, SUM(cnt) OVER (ORDER BY week)
FROM     (SELECT   week, type, COUNT(*) AS cnt
          FROM     customer
          GROUP BY week, type) t
ORDER BY 1 ASC

and the results:

week	type	Sum
2022-01	A	1
2022-01	B	1
2022-02	A	1
2022-02	B	1

issue is here, the last row of the result should be Sum=2, but for some reason (idk why) it follow the above.

Is it other ways to solve and calculate cumulative?

Thank you

CodePudding user response：

SELECT   week, type,
         SUM(cnt) OVER (PARTITION BY week, type
                        ORDER BY week
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       )
FROM     (SELECT   week, type, COUNT(*) AS cnt
          FROM     customer
          GROUP BY week, type) t
ORDER BY 1 ASC

Sentence "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" you can use or not, because it's default behavior.