Often in PostgreSQL you may want to calculate the sum of values in an array column.
You can use the following basic syntax to do so:
SELECT *,
(SELECT SUM(x) FROM UNNEST(points) x) AS sum_points
FROM athletes;
This particular example will calculate the sum of values in each array of the points column in the table named athletes and save the results to a new column named sum_points.
The following example shows how to use this syntax in practice.
Example: How to Calculate Sum of Array Column in PostgreSQL
Suppose that we create the following table named athletes that contains information about the points scored during three consecutive seasons by various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER,
team TEXT,
points NUMERIC[]
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', '{12.1,15.6,17.8}');
INSERT INTO athletes VALUES (0002, 'Hawks', '{22.5,16.5,18.7}');
INSERT INTO athletes VALUES (0003, 'Rockets', '{20.1,24.4,34.6}');
INSERT INTO athletes VALUES (0004, 'Mavs', '{29.4,30.5,31.2}');
INSERT INTO athletes VALUES (0005, 'Mavs', '{12.6,15.5,17.9}');
INSERT INTO athletes VALUES (0006, 'Celtics', '{20.2,23.4,26.6}');
INSERT INTO athletes VALUES (0007, 'Lakers', '{18.8,20.1,23.5}');
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+---------+------------------
1 | Mavs | {12.1,15.6,17.8}
2 | Hawks | {22.5,16.5,18.7}
3 | Rockets | {20.1,24.4,34.6}
4 | Mavs | {29.4,30.5,31.2}
5 | Mavs | {12.6,15.5,17.9}
6 | Celtics | {20.2,23.4,26.6}
7 | Lakers | {18.8,20.1,23.5}
Suppose that we would like to calculate the sum of values in each array in the points column.
We can use the following syntax to do so:
SELECT *,
(SELECT SUM(x) FROM UNNEST(points) x) AS sum_points
FROM athletes;
id | team | points | sum_points
----+---------+------------------+------------
1 | Mavs | {12.1,15.6,17.8} | 45.5
2 | Hawks | {22.5,16.5,18.7} | 57.7
3 | Rockets | {20.1,24.4,34.6} | 79.1
4 | Mavs | {29.4,30.5,31.2} | 91.1
5 | Mavs | {12.6,15.5,17.9} | 46.0
6 | Celtics | {20.2,23.4,26.6} | 70.2
7 | Lakers | {18.8,20.1,23.5} | 62.4
The new column named sum_points contains the sum of values from each corresponding array in the points column.
You can use similar syntax to calculate the sum of values in an array column in your own table.
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 a Rolling Sum
PostgreSQL: How to Calculate Sum of Multiple Columns
