Shape / table · All operations

Summarise only rows that meet a condition

A variant of Summarise by group

Compute an aggregate over just the rows of each group that satisfy a condition, alongside unfiltered aggregates in the same row.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Summarise only the rows of each group that meet a condition, next to unfiltered aggregates

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,
       count(*) FILTER (WHERE amount >= 100) AS big_orders,
       sum(amount) FILTER (WHERE amount >= 100) AS big_total,
       sum(CASE WHEN amount >= 100 THEN amount END) AS big_total_case
FROM orders
GROUP BY product;
SELECT * FROM result ORDER BY product;
SELECT CASE WHEN ((SELECT list((product, orders, total, big_orders, big_total) ORDER BY product) FROM result) IS NOT DISTINCT FROM [('gadget', 3, 140.75, 0, NULL), ('gizmo', 3, 319.99, 1, 220.00), ('widget', 4, 1440.00, 4, 1440.00)] AND (SELECT bool_and(big_total IS NOT DISTINCT FROM big_total_case) FROM result)) THEN 'as expected' ELSE error('the filtered and unfiltered aggregates did not sit side by side: gadget has no big order, gizmo one of 220.00, widget four') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the filtered and unfiltered aggregates did not sit side by side: gadget has no big order, gizmo one of 220.00, widget four”.
  • 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 (3)
  • FILTER (clause)read 2026-09-27

    Restrict the rows one aggregate sees

  • count_ifread 2026-09-27

    Counts the total number of TRUE values for a boolean column

  • countifread 2026-09-27

    Counts the total number of TRUE values for a boolean column

Trifacta (16)
  • ANYIF Functionread 2026-09-27

    Return one non-null value from the rows of the group meeting a condition

  • AVERAGEIF Functionread 2026-09-27

    Mean of the rows of the group meeting a condition

  • COUNTAIF Functionread 2026-09-27

    Count of non-null values among the rows of the group meeting a condition

  • COUNTDISTINCTIF Functionread 2026-09-27

    Count of distinct non-null values among rows meeting a condition

  • COUNTIF Functionread 2026-09-27

    Count of the rows of the group meeting a condition

  • KTHLARGESTIF Functionread 2026-09-27

    The kth largest value among rows meeting a condition

  • KTHLARGESTUNIQUEIF Functionread 2026-09-27

    The kth largest distinct value among rows meeting a condition

  • LISTIF Functionread 2026-09-27

    Collect into an array the values of rows meeting a condition

  • MAXIF Functionread 2026-09-27

    Maximum among the rows of the group meeting a condition

  • MINIF Functionread 2026-09-27

    Minimum among the rows of the group meeting a condition

  • MODEIF Functionread 2026-09-27

    Most frequent value among the rows of the group meeting a condition

  • STDEVIF Functionread 2026-09-27

    Population standard deviation of the rows meeting a condition

  • STDEVSAMPIF Functionread 2026-09-27

    Sample standard deviation of the rows meeting a condition

  • SUMIF Functionread 2026-09-27

    Sum of the rows of the group meeting a condition

  • VARIF Functionread 2026-09-27

    Population variance of the rows meeting a condition

  • VARSAMPIF Functionread 2026-09-27

    Sample variance of the rows meeting a condition

Background

The FILTER clause extends an aggregate function with an additional WHERE clause, so the aggregate is built only from the rows that satisfy it.[1] In DuckDB, FILTER is useful for evaluating several aggregates with different filters in one query, and for creating a pivoted view of a dataset.[2] A FILTER clause can generally be emulated with a CASE expression inside the aggregate function.[1]

When to use it

In DuckDB, FILTER filters the rows fed into an aggregate function in the same way that a WHERE clause filters rows, but only for that aggregate.[2] Use WHERE when every aggregate in the query should see the same rows, and FILTER when only some of them should. (Meridian's view) In DuckDB, rows that satisfy a condition can also be counted without FILTER, with the boolean sum aggregate, such as sum(i <= 5).[2]

In DuckDB, the CASE WHEN approach does not work as expected with an aggregate that does not ignore NULL values, such as first, so FILTER is preferred there.[2] In DuckDB, FILTER does not remove NULL values from rows that satisfy its condition, so omitting them takes an explicit condition such as list(x) FILTER (WHERE x IS NOT NULL).[2] Wrap a filtered sum in coalesce when a group with no matching rows should read zero rather than empty. (Meridian's view)

The method

SQL:2003 introduced the FILTER clause as part of the optional feature Advanced OLAP operations (T612).[1] With the exception of subqueries, window functions and outer references, a FILTER condition may contain any expression allowed in a regular WHERE clause.[1] The FILTER clause works for any aggregate function, including array_agg and ordered-set functions such as listagg.[1] In PostgreSQL, only the input rows for which the filter clause evaluates to true are fed to the aggregate function, and the other rows are discarded.[3] PostgreSQL supports subqueries inside the FILTER clause.[1]

In the CASE emulation, the filter condition goes in the WHEN clause and the value to be aggregated in the THEN clause.[1] Because aggregate functions generally skip null values, the implicit ELSE NULL of the CASE expression is enough to ignore non-matching rows.[1] COUNT(*) needs a non-null constant in the THEN clause instead, as in COUNT(CASE WHEN condition THEN 1 END), because * cannot be put there.[1]

Compared with the alternatives

A HAVING clause in SQL makes a SELECT return only rows whose aggregate values meet its conditions.[4] Reach for HAVING to drop whole groups from the result, and for FILTER to keep every group while narrowing what one aggregate reads. (Meridian's view) In DuckDB, the FILTER clause can pivot data from rows into columns, as a static pivot whose columns must be defined before the query runs.[2]

Sources

  1. [1]The FILTER clausesecondary source
  2. [2]FILTER Clause – DuckDBdocumentation
  3. [3]PostgreSQL: Documentation: 18: 4.2. Value Expressionsdocumentation
  4. [4]Having (SQL) - Wikipediasecondary source