Shape / rows · All operations

Keep groups after aggregating

A variant of Filter rows

Keep the groups whose aggregate values meet a condition.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep the groups whose total meets a condition, after grouping

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT product, count(*) AS orders, sum(amount) AS total
FROM orders
GROUP BY product
HAVING sum(amount) >= 319.99;
SELECT * FROM result ORDER BY product;
SELECT CASE WHEN ((SELECT list((product, orders, total) ORDER BY product) FROM result) = [('gizmo', 3, 319.99), ('widget', 4, 1440.00)]) THEN 'as expected' ELSE error('HAVING did not keep exactly gizmo (319.99, on the boundary) and widget (1440.00) and drop gadget (140.75)') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “HAVING did not keep exactly gizmo (319.99, on the boundary) and widget (1440.00) and drop gadget (140.75)”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)
DuckDB (1)
  • HAVING (clause)read 2026-09-27

    Keep groups matching a condition on an aggregate

Polars (1)
  • GroupBy.havingread 2026-09-27

    Keep the groups that satisfy predicates evaluated over each group

SQLMesh (1)
  • @HAVINGread 2026-09-27

    Include a HAVING clause only when the argument is true

Background

A HAVING clause specifies that an SQL SELECT statement must only return rows where aggregate values meet the specified conditions.[1] The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause, so aggregate functions can be used in it.[2] In DuckDB, the HAVING clause is identical to the WHERE clause in syntax, but WHERE occurs before the grouping and HAVING after it.[3]

When to use it

The WHERE clause filters the rows read from tables at an early stage of a query, and HAVING filters the aggregated results after the rows have been grouped.[1] In PostgreSQL, a query can use both, with WHERE selecting rows by a column that is not grouped and HAVING restricting the output to groups whose aggregate meets a condition.[4] Put a condition on individual rows in WHERE, so the aggregates are computed only over the rows you mean, and keep HAVING for conditions on a group's total, count or average. (Meridian's view)

In PostgreSQL, expressions in a HAVING clause can refer both to grouped expressions and to ungrouped expressions, which necessarily involve an aggregate function.[4] In PostgreSQL, a query with a HAVING clause but no GROUP BY still groups: the result is a single group row, or no rows at all if HAVING eliminates that row.[4]

The method

In SQL, the WHERE clause is applied before the GROUP BY clause, and the HAVING clause acts on the results of the GROUP BY clause.[2] In SQL's logical order of evaluation, the HAVING clause is evaluated after GROUP BY and before the SELECT list, keeping the groups for which it is true.[2] In PostgreSQL, when only certain groups are of interest, the HAVING clause can be used, much like a WHERE clause, to eliminate groups from the result.[4] In PostgreSQL, the aggregate expressions in a HAVING clause do not necessarily need to be the same as those in other parts of the query.[4]

Compared with the alternatives

A HAVING clause is convenient but not necessary, since the same query can be written with the aggregation in a subquery and the condition in an outer WHERE clause.[1] DuckDB's QUALIFY clause filters the results of window functions in a way similar to how a HAVING clause filters the results of aggregate functions.[5]

Sources

  1. [1]Having (SQL) - Wikipediasecondary source
  2. [2]Select (SQL) - Wikipediasecondary source
  3. [3]HAVING Clause – DuckDBdocumentation
  4. [4]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation
  5. [5]QUALIFY Clause – DuckDBdocumentation