Non-aggregate HAVING

Severity: Info

Filtering non-aggregate conditions in HAVING instead of WHERE forces PostgreSQL to group and aggregate all rows before discarding those that don’t match. Placing these conditions in WHERE filters rows before grouping, avoiding the cost of grouping and aggregating rows that will be discarded.

Example

-- Groups ALL departments, then filters
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING department != 'TEMP';

Why it matters

Unnecessary grouping and aggregation

HAVING is evaluated after GROUP BY. When a condition doesn’t reference an aggregate function, PostgreSQL still groups every row and computes aggregates before applying the filter. With WHERE, the matching rows are removed before grouping even begins, so the database groups and aggregates a smaller data set.

PostgreSQL documentation agrees

The PostgreSQL documentation explicitly states: “If a HAVING clause does not use aggregates, it’s seldom useful — the same condition could be used more efficiently at the WHERE stage.”

How to fix it

Move non-aggregate conditions from HAVING to WHERE:

SELECT department, COUNT(*)
FROM employees
WHERE department != 'TEMP'
GROUP BY department;

Detection

Query Doctor flags this nudge when a HAVING clause contains no aggregate function calls (COUNT, SUM, AVG, MIN, MAX, ARRAY_AGG, STRING_AGG, BOOL_AND, BOOL_OR, EVERY). Mixed clauses that combine non-aggregate conditions with aggregate functions are not flagged.