PostgreSQL: Use Aggregate Function with WHERE Clause

Often in PostgreSQL you may want to use an aggregate function in a WHERE clause.

Unfortunately, aggregate functions can’t be used directly in a WHERE clause.

Instead, you must use an aggregate function in a HAVING clause instead.

Here is an example of how to do so:

SELECT team, AVG(points)
FROM athletes
GROUP BY team
HAVING AVG(points) > 20;

This particular example will return the average value in the points column for each unique value in the team column, but only for the rows HAVING an average points value greater than 20.

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

Example: Use Aggregate Function with WHERE Clause 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, 'Mavs', 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, 'Mavs', 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 | 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 | Hawks |     10 |       5
         8 | Mavs  |     14 |      13
         9 | Hawks |     20 |       8
        10 | Hawks |     26 |       3

Suppose that we would like to return the average points scored by players on each team but only for the teams where the average points is greater than 20.

Suppose we attempt to use the following syntax with a WHERE clause to do so:

SELECT team, AVG(points)
FROM athletes
WHERE AVG(points) > 20
GROUP BY team;

psql:commands.sql:26: ERROR:  aggregate functions are not allowed in WHERE
LINE 3: WHERE AVG(points) > 20

We receive an error message that tells us aggregate functions are not allowed in WHERE.

Instead, we must use the following syntax with the HAVING clause instead:

SELECT team, AVG(points)
FROM athletes
GROUP BY team
HAVING AVG(points) > 20;

 team  |         avg         
-------+---------------------
 Mavs  | 20.7500000000000000
 Hawks | 21.5000000000000000

The resulting table shows the average points scored by players on each team, but only for the teams with an average points value greater than 20.

By using the aggregate function in the HAVING clause, we are able to avoid any errors.

Note: In this particular example we used AVG as the aggregate function but you can use any other aggregate function that you’d like such as COUNT, SUM, MAX, MIN, etc.

Additional Resources

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

PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: Count Number of Occurrences of Character in String
PostgreSQL: How to Select Rows Having Count Greater Than