Often in PostgreSQL you may want to use SUM in a WHERE clause so that you only display results for groups that have a sum that meets a specific condition.
You can use the following syntax to do so:
SELECT team, SUM(points) AS sum_points
FROM athletes
GROUP BY team
HAVING SUM(points) > 30;
This particular example will calculate the sum of the points column, grouped by the team column in the table named athletes.
Then, by using the HAVING statement we are able to only show the team names that have a sum in the points column greater than 30.
The following example shows how to use this syntax in practice.
Example: How to Use SUM in WHERE Clause in PostgreSQL
Suppose that we create the following table named athletes that contains information about points scored by various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
team TEXT,
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Celtics', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Nuggets', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points | assists
-----------+---------+--------+---------
1 | Mavs | 22 | 5
2 | Hawks | 30 | 12
3 | Kings | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Celtics | 10 | 5
8 | Celtics | 14 | 13
9 | Nuggets | 20 | 8
10 | Hawks | 26 | 3
Suppose that we would like to calculate the sum of points scored by players on each team.
We can use the following syntax to do so:
SELECT team,
SUM(points) AS sum_points
FROM athletes
GROUP BY team;
team | sum_points
---------+------------
Nuggets | 20
Kings | 37
Mavs | 69
Hawks | 56
Celtics | 24
The sum_points column in the resulting table shows the sum of points scored by players on each team.
Now suppose that we would only like to display the teams that have a value in the sum_points column greater than 30.
We can use the following syntax to do so:
SELECT team, SUM(points) AS sum_points
FROM athletes
GROUP BY team
HAVING SUM(points) > 30;
team | sum_points
-------+------------
Kings | 37
Mavs | 69
Hawks | 56
Notice that this query only returns the rows where the value in the sum_points column is greater than 30.
Feel free to change the condition in the HAVING statement to filter rows based on any specific condition that you would like.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Top N Percent of Records
PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Calculate Average Across Multiple Columns
