PostgreSQL: How to Filter Results After Using GROUP BY

Often in PostgreSQL you may want to filter the results of a query after using the GROUP BY clause.

You can use the HAVING statement with the following syntax to do so:

SELECT team, sum(points)
FROM athletes
GROUP BY team
HAVING sum(points) > 40;

This particular example calculates the sum of values in the points column grouped by the values in the team column and then only returns the rows where the sum of points is greater than 40.

The following example shows how to use this syntax in practice.

Example: How to Filter Results After Using GROUP BY 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
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Celtics', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Hawks', 10);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Hawks', 20);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);

-- view all rows in table
SELECT * FROM athletes;

 athleteid |  team   | points 
-----------+---------+--------
         1 | Celtics |     22
         2 | Hawks   |     30
         3 | Kings   |     12
         4 | Kings   |     25
         5 | Mavs    |     31
         6 | Mavs    |     16
         7 | Hawks   |     10
         8 | Celtics |     14
         9 | Hawks   |     20
        10 | Hawks   |     26

We can use the following syntax to calculate the sum of points scored by each team:

SELECT team, sum(points)
FROM athletes
GROUP BY team;

  team   | sum 
---------+-----
 Kings   |  37
 Mavs    |  47
 Hawks   |  86
 Celtics |  36

Now suppose that we would like to filter these results to only show the teams who had a sum of points greater than 40.

We can use the following syntax to do so:

SELECT team, sum(points)
FROM athletes
GROUP BY team
HAVING sum(points) > 40;

 team  | sum 
-------+-----
 Mavs  |  47
 Hawks |  86

Notice that this only returns the rows for the teams who had a sum of points greater than 40.

You can use similar syntax with the HAVING statement to filter results after using the GROUP BY clause in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Check if Group Contains Null
PostgreSQL: How to Use INNER JOIN with GROUP BY
PostgreSQL: How to Concatenate Strings Using GROUP BY