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

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.