What does a HAVING clause do in a query that has no GROUP BY?

## The implicit single group An aggregate query with no `GROUP BY` is not un-grouped; it is grouped into exactly one group containing every row that survived `WHERE`. That is why `SELECT COUNT(*) FROM orders` returns one row rather than one row per order. Since `HAVING`'s job is to test groups, and this query has a group, `HAVING` is perfectly legal without `GROUP BY`. ```sql SELECT COUNT(*) AS failed_logins FROM audit_log WHERE event = 'LOGIN_FAILED' AND occurred_at >= DATE '2026-08-01' HAVING COUNT(*) > 100; ``` `WHERE` narrows the rows; the survivors form one group; `COUNT(*)` counts them; `HAVING` tests that single count once. ## The behaviour that surprises people An aggregate query without `HAVING` returns **exactly one row, always** — including over an empty input, where `COUNT(*)` is 0 and `SUM(x)` is NULL. Adding `HAVING` breaks that guarantee on purpose: if the predicate is not TRUE, the single group is discarded and the result is an empty set. ```sql -- always one row: either a number or 0 SELECT COUNT(*) FROM audit_log WHERE event = 'LOGIN_FAILED'; -- one row, or none at all SELECT COUNT(*) FROM audit_log WHERE event = 'LOGIN_FAILED' HAVING COUNT(*) > 100; ``` That distinction matters to calling code. A client that does `if (resultSet.next())` behaves very differently between the two forms, and a threshold check written as the second form silently returns "no rows" rather than "0" when nothing matched. ## What may appear in the query With no `GROUP BY` there are no grouping columns, so there is nothing that could be referenced as a plain column: the SELECT list and the `HAVING` predicate may contain only aggregates, literals, expressions over those, and correlated references from an outer query. `SELECT customer_id, COUNT(*) FROM orders HAVING COUNT(*) > 5` is invalid — `customer_id` has many values inside the single group. As elsewhere, `HAVING` may test an aggregate that is not selected: ```sql SELECT AVG(amount) AS avg_amount FROM orders WHERE order_date >= DATE '2026-01-01' HAVING COUNT(*) >= 30; -- only report the average if the sample is big enough ``` That reads as a guard: publish the average only when at least 30 observations back it. ## The same predicate is not a WHERE predicate Beginners sometimes try to compress this into `WHERE COUNT(*) > 100`, which is rejected: `WHERE` runs before the implicit group exists. The presence or absence of `GROUP BY` changes nothing about that — an aggregate is never legal in `WHERE`. ## Where you actually meet it The form shows up in threshold and alerting queries ("emit a row only if the error count crossed the limit"), in existence-style probes over aggregates, and inside subqueries where returning zero rows instead of a zero is exactly what an `EXISTS` or `NOT EXISTS` test wants. It also appears in generated SQL, where a reporting layer appends a `HAVING` filter without knowing whether the query grouped anything. ## Style note Because the shape is uncommon, spell out the intent when you use it: a comment such as `-- returns no rows unless the threshold is crossed` saves the next reader from assuming a missing `GROUP BY`. When you want a value rather than a presence signal, drop the `HAVING` and let the single row report the number, or wrap the query so the caller always gets a row.