HAVING ​

HAVING filters the groups produced by an aggregate query. It can use aggregate expressions, such as sum(amount) > 100, because it runs after the group has been calculated.

Use WHERE to filter individual input rows before aggregation. A query can use both clauses.

Syntax ​

SELECT grouping_key, aggregate_function(value)
FROM source
[WHERE row_condition]
GROUP BY grouping_key
HAVING aggregate_condition

A group is retained only when the condition is SQL true. False and NULL conditions remove the group.

Example ​

Regions with enough paid revenue ​

WHERE excludes pending orders; HAVING then excludes regions whose paid total is too small:

CREATE TABLE orders (region TEXT, status TEXT, amount INTEGER);
INSERT INTO orders VALUES ('East', 'paid', 60),
           ('East', 'paid', 70),
           ('West', 'paid', 40),
           ('West', 'pending', 200);

SELECT region, sum(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY region
HAVING sum(amount) >= 100
ORDER BY region
FORMAT csv;

"East",130

See GROUP BY for grouping expressions and aggregate functions for the calculations available in HAVING.