How to fix Aggregate functions are not allowed in WHERE in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 28, 2025· 174 views

PostgreSQL doesn't allow aggregate functions (COUNT, SUM, AVG, MAX, MIN) directly in the WHERE clause. This happens because WHERE filters rows before aggregation is computed. Use the HAVING clause instead to filter aggregated results.

Quick fix

Fixes 42803

-- WRONG: Aggregate function in WHERE
SELECT category, COUNT(*) as count
FROM products
WHERE COUNT(*) > 5
GROUP BY category;

-- CORRECT: Aggregate function in HAVING
SELECT category, COUNT(*) as count
FROM products
GROUP BY category
HAVING COUNT(*) > 5;
  1. Understand the difference between WHERE and HAVING
  2. Move aggregate functions from WHERE to HAVING
  3. Use a subquery for complex aggregate filtering
  4. Apply non-aggregate conditions in WHERE, aggregate conditions in HAVING
postgresql — error
-- WRONG: Aggregate function in WHERE
SELECT category, COUNT(*) as count
FROM products
WHERE COUNT(*) > 5
GROUP BY category;
-- CORRECT: Aggregate function in HAVING
SELECT category, COUNT(*) as count
FROM products
GROUP BY category
HAVING COUNT(*) > 5;
Aggregate functions are not allowed in WHERE

What this error means

This error occurs when you attempt to use an aggregate function like COUNT(), SUM(), AVG(), MAX(), or MIN() directly in a WHERE clause. PostgreSQL evaluates the WHERE clause before any grouping or aggregation takes place, so aggregate functions haven't been computed yet at that stage. The WHERE clause is meant for filtering individual rows, while aggregate functions operate on groups of rows.

Typical symptoms

  • Query execution fails with 'aggregate functions are not allowed in WHERE' error
  • Queries using COUNT, SUM, AVG, MAX, or MIN in WHERE clause throw error code 42803
  • Attempts to filter rows based on aggregate calculations fail
  • Error appears when writing queries like: WHERE COUNT(*) > 5 or WHERE SUM(amount) > 1000

Common causes

  1. Using an aggregate function directly in the WHERE clause instead of HAVING
  2. Misunderstanding the SQL query execution order (FROM → WHERE → GROUP BY → SELECT)
  3. Trying to filter aggregated data without using GROUP BY and HAVING together
  4. Using aggregate functions on grouped data without proper HAVING clause

How to fix "Aggregate functions are not allowed in WHERE"

1Understand the difference between WHERE and HAVING

WHERE filters individual rows BEFORE grouping and aggregation. HAVING filters grouped rows AFTER aggregation is computed. Since WHERE is evaluated first, it cannot reference aggregate functions.

2Move aggregate functions from WHERE to HAVING

Convert your query to use HAVING instead of WHERE for aggregate conditions:

sql
-- WRONG: Aggregate function in WHERE
SELECT category, COUNT(*) as count
FROM products
WHERE COUNT(*) > 5
GROUP BY category;

-- CORRECT: Aggregate function in HAVING
SELECT category, COUNT(*) as count
FROM products
GROUP BY category
HAVING COUNT(*) > 5;
3Use a subquery for complex aggregate filtering

For more complex scenarios where you need to filter based on aggregate results without grouping, use a subquery:

sql
-- Find the city with the maximum temperature
SELECT city, temp
FROM weather
WHERE temp = (SELECT MAX(temp_lo) FROM weather);

-- Find orders where total exceeds the average
SELECT order_id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);
4Apply non-aggregate conditions in WHERE, aggregate conditions in HAVING

Combine WHERE and HAVING clauses properly:

sql
-- Filter individual rows with WHERE, then aggregate with GROUP BY,
-- then filter aggregated results with HAVING
SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date > '2020-01-01'  -- Filter rows before aggregation
GROUP BY department
HAVING AVG(salary) > 50000;      -- Filter groups after aggregation

Advanced notes

The SQL standard specifies that WHERE is evaluated before GROUP BY and aggregate functions are computed. This is why PostgreSQL (and most SQL databases) disallow aggregate functions in WHERE. If you need to filter based on aggregates of subsets, consider using Common Table Expressions (CTEs) with WITH clauses for better readability. Window functions (OVER clause) can also provide alternatives for some use cases where you need row-by-row comparisons with aggregate values.

Frequently asked questions

What does "42803" mean?
This error occurs when you attempt to use an aggregate function like COUNT(), SUM(), AVG(), MAX(), or MIN() directly in a WHERE clause. PostgreSQL evaluates the WHERE clause before any grouping or aggregation takes place, so aggregate functions haven't been computed yet at that stage. The WHERE clause is meant for filtering individual rows, while aggregate functions operate on groups of rows.
How do I fix "42803"?
PostgreSQL doesn't allow aggregate functions (COUNT, SUM, AVG, MAX, MIN) directly in the WHERE clause. This happens because WHERE filters rows before aggregation is computed. Use the HAVING clause instead to filter aggregated results.
What causes "42803"?
Using an aggregate function directly in the WHERE clause instead of HAVING Misunderstanding the SQL query execution order (FROM → WHERE → GROUP BY → SELECT) Trying to filter aggregated data without using GROUP BY and HAVING together
Is the "42803" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading