Often in PostgreSQL you may want to calculate a cumulative sum for a specific column in your table.
You can use one of the following method to do so:
Method 1: Calculate Cumulative Sum
SELECT *,
SUM(points) OVER (ORDER BY id) AS cum_points
FROM athletes
ORDER BY id;
This particular example will calculate the cumulative sum of values in the points column of the athletes table and save the results to a new column named cum_points.
Method 2: Calculate Cumulative Sum by Group
SELECT *,
SUM(points) OVER (PARTITION BY team
ORDER BY id) AS cum_points
FROM athletes
ORDER BY id;
This particular example will calculate the cumulative sum of values in the points column of the athletes table, grouped by the values in the team column and save the results to a new column named cum_points.
The following example shows how to use both methods in practice with the following table named athletes that contains information about points scored by basketball players on various teams:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER PRIMARY KEY,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 33);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+------+--------
1 | Mavs | 22
2 | Mavs | 30
3 | Mavs | 12
4 | Mavs | 9
5 | Mavs | 14
6 | Heat | 16
7 | Heat | 40
8 | Heat | 18
9 | Heat | 16
10 | Heat | 33
Example 1: Calculate Cumulative Sum in PostgreSQL
We can use the following syntax to create a new column named cum_points that calculates the cumulative sum of values in the points column:
SELECT *,
SUM(points) OVER (ORDER BY id) AS cum_points
FROM athletes
ORDER BY id;
id | team | points | cum_points
----+------+--------+------------
1 | Mavs | 22 | 22
2 | Mavs | 30 | 52
3 | Mavs | 12 | 64
4 | Mavs | 9 | 73
5 | Mavs | 14 | 87
6 | Heat | 16 | 103
7 | Heat | 40 | 143
8 | Heat | 18 | 161
9 | Heat | 16 | 177
10 | Heat | 33 | 210
Notice that the new column named cum_points contains the cumulative sum of values in the points column.
Example 2: Calculate Cumulative Sum by Group in PostgreSQL
We can use the following syntax to create a new column named cum_points that calculates the cumulative sum of values in the points column:
SELECT *,
SUM(points) OVER (PARTITION BY team
ORDER BY id) AS cum_points
FROM athletes
ORDER BY id;
id | team | points | cum_points
----+------+--------+------------
1 | Mavs | 22 | 22
2 | Mavs | 30 | 52
3 | Mavs | 12 | 64
4 | Mavs | 9 | 73
5 | Mavs | 14 | 87
6 | Heat | 16 | 16
7 | Heat | 40 | 56
8 | Heat | 18 | 74
9 | Heat | 16 | 90
10 | Heat | 33 | 123
Notice that the new column named cum_points contains the cumulative sum of values in the points column, grouped by the values in the team column.
For example, once the team names switch from “Mavs” to “Heat” then the value in the cum_points column starts over.
Note: By using the PARTITION BY statement we are able to specify a column that should be used for grouping.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Calculate Sum by Group
PostgreSQL: How to Calculate Sum of Multiple Columns
PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Calculate Average Across Multiple Columns
