PostgreSQL: How to Calculate Average with Condition

Often in PostgreSQL you may want to calculate an average value with a condition.

You can use the following syntax to do so:

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

This particular example will calculate the average value of the points column by only using rows where the value in the points column is greater than zero, grouped by the team column.

By using the FILTER and the WHERE commands, we are able to calculate an average value by only using rows that meet a specific condition.

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

Example: How to Calculate Average with Condition 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 (
  id INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 33);

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

 id | team | points 
----+------+--------
  1 | Mavs |     22
  2 | Mavs |      0
  3 | Mavs |     12
  4 | Mavs |      9
  5 | Mavs |     14
  6 | Heat |     16
  7 | Heat |     40
  8 | Heat |      0
  9 | Heat |     16
 10 | Heat |      0

Suppose that we would like to calculate the average number of points scored by players on each team by only considering the players that scored more than zero points.

We can use the following syntax to do so:

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

 team |         avg         
------+---------------------
 Heat | 24.0000000000000000
 Mavs | 14.2500000000000000

From the resulting table we can see:

• The average points scored by players on the Heat (who scored more than zero points) is 24.
• The average points scored by players on the Mavs (who scored more than zero points)  is 14.25.

You can use similar syntax to calculate an average based on a condition in your own table.

Additional Resources

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

PostgreSQL: How to Calculate Average by Group
PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Calculate Average Across Multiple Columns