Often in PostgreSQL you may want to calculate the average value across multiple columns.
You can use the following basic syntax to do so:
SELECT *,
(SELECT AVG(c)
FROM (VALUES(points_g1),
(points_g2),
(points_g3)) T (c)) AS avg
FROM athletes;
This particular example will calculate the average value across the columns named points_g1, points_g2 and points_g3 of the table named athletes.
Note: The T is used as an alias of the derived table and C is the alias of the specific column.
The following example shows how to use this syntax in practice.
Example: How to Calculate Average Across 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 players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
points_g1 INTEGER,
points_g2 INTEGER,
points_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, 20, 8, 12);
INSERT INTO athletes VALUES (0010, 26, 3, 20);
-- view all rows in table
SELECT * FROM athletes;
athleteid | points_g1 | points_g2 | points_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 | 20 | 8 | 12
10 | 26 | 3 | 20
Suppose that we would like to calculate the average points scored for each player by calculating the average across the points_g1, points_g2 and points_g3 columns.
We can use the following syntax to do so:
SELECT *,
(SELECT AVG(c)
FROM (VALUES(points_g1),
(points_g2),
(points_g3)) T (c)) AS avg
FROM athletes;
athleteid | points_g1 | points_g2 | points_g3 | avg
-----------+-----------+-----------+-----------+---------------------
1 | 22 | 5 | 8 | 11.6666666666666667
2 | 30 | 12 | 10 | 17.3333333333333333
3 | 12 | 3 | 6 | 7.0000000000000000
4 | 25 | 9 | 34 | 22.6666666666666667
5 | 31 | 14 | 18 | 21.0000000000000000
6 | 16 | 3 | 7 | 8.6666666666666667
7 | 10 | 5 | 9 | 8.0000000000000000
8 | 14 | 13 | 12 | 13.0000000000000000
9 | 20 | 8 | 12 | 13.3333333333333333
10 | 26 | 3 | 20 | 16.3333333333333333
The new column named avg contains the average points scored by each player across the three games.
For example, we can see:
- The first player scored an average of (22+5+8) / 3 = 11.667 points per game.
- The second player scored an average of (30+12+10) / 3 = 17.333 points per game.
- The third player scored an average of (12+3+6) / 3 = 7 points per game.
And so on.
Feel free to use similar syntax to calculate the average across 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 Round Numbers to 2 Decimal Places
PostgreSQL: Count Occurrences of Each Value in Column
