HAVING clause
The HAVING clause filters grouped results after aggregation, letting you keep or discard groups based on conditions over aggregate values like SUM() or COUNT().
Overview
HAVING filters the output of a GROUP BY clause. While WHERE filters individual rows before grouping, HAVING filters entire groups after their aggregate values (SUM, COUNT, AVG, etc.) have been computed. It exists specifically to let you write conditions like "only show customers whose total spend exceeds $1,000" -- something WHERE cannot express because the aggregate doesn't exist yet at the row level.
Syntax and example
Copy code
SELECT customer_id, SUM(order_total) AS total_spent, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) > 1000
AND COUNT(*) >= 3;
Here, WHERE could still be added to the same query to pre-filter rows (e.g., WHERE status = 'completed') before grouping, while HAVING filters the resulting groups.
HAVING without GROUP BY
HAVING can technically appear without an explicit GROUP BY; in that case the entire result set is treated as a single group:
Copy code
SELECT SUM(order_total) AS total_revenue
FROM orders
HAVING SUM(order_total) > 1000000;
DuckDB notes
DuckDB's "friendly SQL" extensions let HAVING reference an aggregate alias defined in the SELECT list directly, instead of repeating the full aggregate expression:
Copy code
SELECT customer_id, SUM(order_total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 1000;
This works because DuckDB resolves non-aggregate aliases in WHERE/GROUP BY and aggregate aliases in HAVING, within the same query, avoiding an extra subquery or CTE that other databases often require.
Evaluation order
Query clauses execute logically as FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT, which is why HAVING can reference aggregates but WHERE cannot.
Related terms
GROUP BY organizes rows into groups for aggregate calculations like SUM, COUNT, and AVG. Learn single and multi-column grouping, HAVING filters, and GROUP BY ALL.
WHERE clause →The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.
QUALIFY →QUALIFY filters rows based on the result of a window function, letting you write conditions like 'keep only the top-ranked row per group' without wrapping the query in a subquery.
FILTER clause →The FILTER clause restricts which rows an aggregate function processes, letting you compute multiple conditional aggregates in a single GROUP BY query without CASE expressions.
filter →A filter operation selectively includes or excludes records from a dataset based on one or more conditions.
aggregate functions →Aggregate functions are SQL operations that perform calculations across multiple rows of data to produce a single summary value.
FAQS
Yes. Without a GROUP BY, HAVING treats the whole table as a single group, which is useful for filtering on an aggregate computed over the entire result set.
Any non-aggregated column referenced in HAVING (or SELECT) must also appear in the GROUP BY list; HAVING can freely reference aggregate expressions but not arbitrary row-level columns.
