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.
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.
The correct solution is to use the HAVING clause instead of WHERE when filtering on aggregate results:
-- 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.
If you prefer not to use HAVING, wrap your aggregation in a subquery:
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.
For larger or more readable queries, use a WITH clause to separate aggregation from filtering:
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.
Remember the execution order when designing queries with both WHERE and HAVING:
-- 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.
If you need a single aggregate value without grouping, filter with WHERE before the aggregation:
-- 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.
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.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL