Often in PostgreSQL you may want to calculate the sum of multiple columns.
You can use the following basic syntax to do so:
SELECT *,
COALESCE(g1,0) + COALESCE(g2,0) + COALESCE(g3,0) AS sum_points
FROM athletes;
This particular example will calculate the sum of values across the columns named g1, g2 and g3 of the table named athletes and save the results to a new column named sum_points.
Note: We use the COALESCE command to convert any NULL values in each column to zero. Without doing this, any NULL values that are summed together will produce NULL as a result.
The following example shows how to use this syntax in practice.
Example: How to Calculate Sum of Multiple Columns in PostgreSQL
Suppose that we create the following table named athletes that contains information about the points scored during three consecutive games by various basketball player:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
g1 INTEGER,
g2 INTEGER,
g3 INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 22, 5, 8);
INSERT INTO athletes VALUES (0002, 30, 12, 10);
INSERT INTO athletes VALUES (0003, 12, 3, 6);
INSERT INTO athletes VALUES (0004, 25, 9, 34);
INSERT INTO athletes VALUES (0005, 31, 14, 18);
INSERT INTO athletes VALUES (0006, 16, 3, 7);
INSERT INTO athletes VALUES (0007, 10, 5, 9);
INSERT INTO athletes VALUES (0008, 14, 13, 12);
INSERT INTO athletes VALUES (0009, NULL, 8, 12);
INSERT INTO athletes VALUES (0010, 26, 3, 20);
-- view all rows in table
SELECT * FROM athletes;
athleteid | g1 | g2 | g3
-----------+----+----+----
1 | 22 | 5 | 8
2 | 30 | 12 | 10
3 | 12 | 3 | 6
4 | 25 | 9 | 34
5 | 31 | 14 | 18
6 | 16 | 3 | 7
7 | 10 | 5 | 9
8 | 14 | 13 | 12
9 | | 8 | 12
10 | 26 | 3 | 20
Suppose that we would like to calculate the total points scored for each player by calculating the sum across the g1, g2 and g3 columns.
We can use the following syntax to do so:
SELECT *,
COALESCE(g1,0) + COALESCE(g2,0) + COALESCE(g3,0) AS sum_points
FROM athletes;
athleteid | g1 | g2 | g3 | sum_points
-----------+----+----+----+------------
1 | 22 | 5 | 8 | 35
2 | 30 | 12 | 10 | 52
3 | 12 | 3 | 6 | 21
4 | 25 | 9 | 34 | 68
5 | 31 | 14 | 18 | 63
6 | 16 | 3 | 7 | 26
7 | 10 | 5 | 9 | 24
8 | 14 | 13 | 12 | 39
9 | | 8 | 12 | 20
10 | 26 | 3 | 20 | 49
The new column named sum_points contains the sum of points scored by each player across the three games.
For example, we can see:
- The first player scored a total of 22 + 5 + 8 = 35 points.
- The second player scored a total of 30 + 12 + 10 = 52 points.
- The third player scored a total of 12 + 3 + 6 = 21 points.
And so on.
Feel free to use similar syntax to calculate the sum of multiple columns in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Calculate Sum by Group
PostgreSQL: How to Calculate Average Across Multiple Columns
PostgreSQL: Count Occurrences of Each Value in Column
