How to fix aggregate functions are not allowed in WHERE clause in PostgreSQL

PostgreSQLBEGINNERMEDIUMUpdated Dec 29, 2025· 222 views

PostgreSQL prevents aggregate functions like SUM(), COUNT(), AVG(), or MAX() in WHERE clauses because WHERE filters rows before aggregation occurs. To filter on aggregate results, use the HAVING clause after GROUP BY, or wrap your query in a subquery or CTE to compute aggregates first.

Quick fix

Fixes aggregate functions are not allowed in WHERE clause

-- Wrong (causes error):
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
WHERE COUNT(*) > 5;

-- Correct:
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
  1. Replace WHERE with HAVING to filter on grouped aggregates
  2. Use a subquery to compute aggregates and filter in the outer query
  3. Use a CTE (Common Table Expression) for more complex scenarios
  4. Use aggregate functions correctly: WHERE before GROUP BY, HAVING after
postgresql — error
-- Wrong (causes error):
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
WHERE COUNT(*) > 5;
-- Correct:
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
aggregate functions are not allowed in WHERE clause

What this error means

Aggregate functions in PostgreSQL (such as SUM(), COUNT(), AVG(), MAX(), MIN(), and others) compute a single result value from multiple input rows. These functions can only appear in the SELECT list, HAVING clause, or ORDER BY clause—not in WHERE clauses. This restriction exists because of SQL's logical query execution order: the WHERE clause filters individual rows before any grouping or aggregation happens, so the aggregate results don't yet exist when WHERE is evaluated. When PostgreSQL encounters an aggregate function in a WHERE clause, it immediately rejects the query with this error. The difference between WHERE and HAVING is fundamental: WHERE filters rows before aggregation, while HAVING filters groups after aggregation. Understanding this distinction is key to writing correct SQL.

Typical symptoms

  • Query fails with "ERROR: aggregate functions are not allowed in WHERE clause" when trying to filter on SUM(), COUNT(), or similar functions
  • Attempting queries like "WHERE COUNT(*) > 5" or "WHERE SUM(amount) > 1000" causes immediate syntax error
  • Complex aggregation queries with GROUP BY cannot be filtered by aggregate values directly in WHERE
  • Migration from other databases (like MySQL or SQL Server) that may handle aggregate functions differently fails in PostgreSQL
  • ORM-generated queries that attempt to filter on calculated aggregate columns produce this error

Common causes

  1. Direct use of aggregate functions like COUNT(), SUM(), AVG() in WHERE clause conditions
  2. Misunderstanding SQL execution order - WHERE evaluates before aggregates are calculated
  3. Attempting to use WHERE to filter groups instead of using the HAVING clause
  4. Confusion between filtering individual rows (WHERE) and filtering grouped results (HAVING)
  5. Porting queries from other databases without modification for PostgreSQL syntax requirements
  6. ORM or query builder generating SQL that incorrectly places aggregates in WHERE

How to fix "aggregate functions are not allowed in WHERE clause"

1Replace WHERE with HAVING to filter on grouped aggregates

The correct solution is to use the HAVING clause instead of WHERE when filtering on aggregate results:

sql
-- Wrong (causes error):
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
WHERE COUNT(*) > 5;

-- Correct:
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

The HAVING clause is designed to filter groups after aggregation. Use WHERE to filter individual rows before grouping, and HAVING to filter groups based on aggregate values after grouping.

2Use a subquery to compute aggregates and filter in the outer query

If you prefer not to use HAVING, wrap your aggregation in a subquery:

sql
SELECT department, employee_count
FROM (
  SELECT department, COUNT(*) as employee_count
  FROM employees
  GROUP BY department
) dept_counts
WHERE employee_count > 5;

This approach computes the aggregates in the inner query, then applies WHERE filtering to those calculated values in the outer query. Subqueries are equivalent to HAVING but can be more explicit about the filtering logic.

3Use a CTE (Common Table Expression) for more complex scenarios

For larger or more readable queries, use a WITH clause to separate aggregation from filtering:

sql
WITH dept_stats AS (
  SELECT department,
         COUNT(*) as employee_count,
         AVG(salary) as avg_salary,
         MAX(salary) as max_salary
  FROM employees
  GROUP BY department
)
SELECT *
FROM dept_stats
WHERE employee_count > 5
  AND avg_salary > 50000;

CTEs make complex aggregations easier to read and understand. Define the aggregated data in the WITH clause, then use WHERE in the main query to filter on those calculated columns. This approach scales well for queries with multiple aggregates and conditions.

4Use aggregate functions correctly: WHERE before GROUP BY, HAVING after

Remember the execution order when designing queries with both WHERE and HAVING:

sql
-- Find departments with high-earning employees
SELECT department, COUNT(*) as total_employees, AVG(salary) as avg_salary
FROM employees
WHERE hire_date > '2020-01-01'      -- Filter INDIVIDUAL rows before grouping
GROUP BY department
HAVING AVG(salary) > 60000          -- Filter GROUPS based on aggregates
  AND COUNT(*) >= 10
ORDER BY avg_salary DESC;

WHERE filters the rows that go into aggregation, while HAVING filters the groups created by GROUP BY. You can use both in the same query: WHERE first, then GROUP BY, then HAVING. This pattern is essential for complex analytical queries.

5For non-grouped aggregates, use WHERE on non-aggregate columns

If you need a single aggregate value without grouping, filter with WHERE before the aggregation:

sql
-- Get average salary for employees hired recently
SELECT AVG(salary) as avg_recent_salary
FROM employees
WHERE hire_date > '2020-01-01';

-- Don't try this:
-- SELECT AVG(salary) as avg_recent_salary
-- FROM employees
-- WHERE AVG(salary) > 50000;  -- ERROR!

For aggregate results across the entire table, use WHERE to filter the rows that participate in aggregation, not to filter the aggregate values themselves.

Advanced notes

Aggregate functions process input rows in stages: first WHERE filters individual rows, then GROUP BY partitions the filtered rows, then aggregate functions compute results over each group, finally HAVING filters the grouped results. This execution order is why aggregates cannot appear in WHERE. Window functions (like SUM() OVER()) also cannot appear in WHERE, but they use a different execution phase and have the QUALIFY clause (PostgreSQL 15+) as an alternative. Performance-wise, WHERE filters are typically faster than HAVING because they reduce rows before aggregation; use HAVING only when you need to filter on aggregate values. For very complex aggregations, consider whether window functions or materialized views might express your query more efficiently. When migrating from MySQL, remember that MySQL has historically been more lenient with non-standard SQL allowing aggregates in WHERE; PostgreSQL strictly enforces standard SQL restrictions.

Frequently asked questions

What does "aggregate functions are not allowed in WHERE clause" mean?
Aggregate functions in PostgreSQL (such as SUM(), COUNT(), AVG(), MAX(), MIN(), and others) compute a single result value from multiple input rows. These functions can only appear in the SELECT list, HAVING clause, or ORDER BY clause—not in WHERE clauses. This restriction exists because of SQL's logical query execution order: the WHERE clause filters individual rows before any grouping or aggregation happens, so the aggregate results don't yet exist when WHERE is evaluated.
How do I fix "aggregate functions are not allowed in WHERE clause"?
PostgreSQL prevents aggregate functions like SUM(), COUNT(), AVG(), or MAX() in WHERE clauses because WHERE filters rows before aggregation occurs. To filter on aggregate results, use the HAVING clause after GROUP BY, or wrap your query in a subquery or CTE to compute aggregates first.
What causes "aggregate functions are not allowed in WHERE clause"?
Direct use of aggregate functions like COUNT(), SUM(), AVG() in WHERE clause conditions Misunderstanding SQL execution order - WHERE evaluates before aggregates are calculated Attempting to use WHERE to filter groups instead of using the HAVING clause
Is the "aggregate functions are not allowed in WHERE clause" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading