Here in this post I will be explaining you about the query to get the cumulative sum of the rows based on the previous rows. many times you will need to get this for the reporting purpose.
Sample result set :-
Expected Output Result :-
Below is the query which you can use to get the desired output as mentioned above.
SELECT ITEM,
WEEKSTART,
COUNTRY,
REGION,
SUM(QUANTITY) OVER (PARTITION BY item ORDER BY item,weekstart,country ROWS UNBOUNDED PRECEDING) AS csum
FROM table_name
ORDER BY ITEM,WEEKSTART,COUNTRY
Sample result set :-
| item | weekstart | country | region | quantity | |
| AAAA | 9/11/2017 | IN | India | ||
| AAAA | 9/18/2017 | IN | India | ||
| AAAA | 9/25/2017 | IN | India | ||
| AAAA | 10/2/2017 | IN | India | 2000 | |
| AAAA | 10/9/2017 | IN | India | 3000 | |
| AAAA | 10/16/2017 | IN | India | ||
| AAAA | 10/23/2017 | IN | India | ||
| AAAA | 10/30/2017 | IN | India |
Expected Output Result :-
| item | weekstart | country | region | quantity | csum |
| AAAA | 9/11/2017 | IN | India | ||
| AAAA | 9/18/2017 | IN | India | ||
| AAAA | 9/25/2017 | IN | India | ||
| AAAA | 10/2/2017 | IN | India | 2000 | 2000 |
| AAAA | 10/9/2017 | IN | India | 3000 | 5000 |
| AAAA | 10/16/2017 | IN | India | 5000 | |
| AAAA | 10/23/2017 | IN | India | 5000 | |
| AAAA | 10/30/2017 | IN | India | 5000 | |
Redshift Query :-
SELECT ITEM,
WEEKSTART,
COUNTRY,
REGION,
SUM(QUANTITY) OVER (PARTITION BY item ORDER BY item,weekstart,country ROWS UNBOUNDED PRECEDING) AS csum
FROM table_name
ORDER BY ITEM,WEEKSTART,COUNTRY
No comments:
Post a Comment