Often in PostgreSQL you may want to use SUMIF logic to only sum rows in a table that meet one or more conditions.
You can use the following methods to do so:
Method 1: Sum Rows that Meet One Condition
SELECT SUM(points)
FROM athletes
WHERE team = 'Nuggets';
This particular example calculates the sum of values in the points column of the athletes table where the value in the team column is equal to Nuggets.
Method 2: Sum Rows that Meet Multiple Conditions
SELECT SUM(points)
FROM athletes
WHERE team = 'Nuggets' AND points > 15;
This particular example calculates the sum of values in the points column of the athletes table where the value in the team column is equal to Nuggets and the value in the points column is greater than 15.
The following example shows how to use each of these methods in practice.
Example: How to Use SUMIF in PostgreSQL
Suppose that we create the following table named athletes that contains information about 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, 'Celtics', 10, 5);
INSERT INTO athletes VALUES (0006, 'Nuggets', 14, 13);
INSERT INTO athletes VALUES (0007, 'Nuggets', 20, 8);
INSERT INTO athletes VALUES (0008, 'Nuggets', 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 | Celtics | 10 | 5
6 | Nuggets | 14 | 13
7 | Nuggets | 20 | 8
8 | Nuggets | 26 | 3
We can use the following syntax to calculate the sum of values in the points column where the value in the team column is equal to Nuggets:
SELECT SUM(points)
FROM athletes
WHERE team = 'Nuggets';
sum
-----
60
From the output we can see that the sum of values in the points column where the value in the team column is equal to Nuggets is 60.
We can also use the following syntax to calculate the sum of values in the points column for the rows where the value in the team column is equal to Nuggets and the value in the points column is greater than 15:
SELECT SUM(points)
FROM athletes
WHERE team = 'Nuggets' AND points > 15;
sum
-----
46
From the output we can see that the sum of values in the points column where the value in the team column is equal to Nuggets and the value in the points column is greater than 15 is 46.
Feel free to use similar syntax to perform a SUMIF in your own table in PostgreSQL.
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 Sum of Array Column
