Shape / table · All operations

Summarise the whole table

A variant of Summarise by group

Reduce the entire table to a single summary row, without grouping keys.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Summarise the whole table into one row

It reads the orders and customers tables the proofs share. Create them 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);

CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL
CREATE TEMP TABLE summary AS
SELECT count(*) AS orders, count(amount) AS priced, sum(amount) AS total, round(avg(amount), 2) AS mean,
       min(amount) AS smallest, max(amount) AS largest, count(DISTINCT customer) AS customers,
       min(order_date) AS first_day, max(order_date) AS last_day
FROM orders;
-- an empty input still gives one row
CREATE TEMP TABLE from_nothing AS SELECT count(*) AS orders, sum(amount) AS total FROM orders WHERE false;
SELECT * FROM summary;
SELECT CASE WHEN ((SELECT count(*) FROM summary) = 1 AND (SELECT (orders, priced, total, mean, smallest, largest, customers, first_day, last_day) FROM summary) = (10, 9, 1900.74, 211.19, 15.00, 510.00, 8, DATE '2026-01-03', DATE '2026-02-10') AND (SELECT count(*) FROM from_nothing) = 1 AND (SELECT orders FROM from_nothing) = 0 AND (SELECT total FROM from_nothing) IS NULL) THEN 'as expected' ELSE error('the table did not reduce to one row of 10 orders, 9 priced, totalling 1900.74, or an empty table did not give one row of zero') 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 table did not reduce to one row of 10 orders, 9 priced, totalling 1900.74, or an empty table did not give one row of zero”.
  • 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).

Alteryx Designer (1)
  • Count Recordsread 2026-09-27

    Return the number of records passing through, including a count of zero

Apache DataFusion (1)
  • countread 2026-09-27

    DataFrame method: Return the total number of rows

Duckle (2)
  • Count Rowsread 2026-09-25

    count rows

  • Diff Summaryread 2026-09-25

    reduce a change feed to one row of added, removed and updated counts

Polars (10)
  • DataFrame.countread 2026-09-27

    Count the non-null values of every column, giving a one-row frame

  • DataFrame.maxread 2026-09-27

    Reduce every column to its maximum, giving a one-row frame

  • DataFrame.meanread 2026-09-27

    Reduce every column to its mean, giving a one-row frame

  • DataFrame.medianread 2026-09-27

    Reduce every column to its median, giving a one-row frame

  • DataFrame.minread 2026-09-27

    Reduce every column to its minimum, giving a one-row frame

  • DataFrame.productread 2026-09-27

    Reduce every column to the product of its values, giving a one-row frame

  • DataFrame.quantileread 2026-09-27

    Reduce every column to a chosen quantile, giving a one-row frame

  • DataFrame.stdread 2026-09-27

    Reduce every column to its standard deviation, giving a one-row frame

  • DataFrame.sumread 2026-09-27

    Reduce every column to its sum, giving a one-row frame

  • DataFrame.varread 2026-09-27

    Reduce every column to its variance, giving a one-row frame

RapidMiner Studio (2)
  • Extract Aggregatesread 2026-09-27

    Compute aggregate features (sum, mean, min, max, deviation, etc.) for each series column

  • Extract Moderead 2026-09-27

    Compute the most frequent values of each series column

VisiData (2)
  • freq-summaryread 2026-09-23

    Open one-line summary with aggregates for all rows and for selected rows

  • memo-aggregateread 2026-09-23

    Aggregate current column over selected rows; store the result in the Memory Sheet

Background

In relational algebra, the grouping attributes of an aggregation are optional, and without them the aggregate functions apply across the entire relation.[1] In SQLite, an aggregate query without a GROUP BY clause always returns exactly one row of data, even if there are zero rows of input data.[2] In PostgreSQL, a query with aggregate function calls but no GROUP BY clause still groups, and its result is a single group row.[3]

When to use it

In PostgreSQL, the general-purpose aggregates except count return null when no rows are selected, so sum of no rows returns null, not zero.[4] In PostgreSQL, the coalesce function can be used to substitute zero or an empty array for that null when necessary.[4] Read a NULL total from an empty input as nothing to add up, and turn it into zero only where a report needs a number. (Meridian's view)

In SQLite, each non-aggregate expression in an aggregate query without GROUP BY is evaluated for an arbitrarily selected row, or against a row of NULLs if the input is empty.[2] In PostgreSQL, a HAVING clause can eliminate the single group row, so a whole-table aggregate with HAVING may return no rows at all.[3] A one-row summary of row count, total and date range makes a quick check after each load, to compare with the source before trusting the table. (Meridian's view)

The method

In SQLite, each aggregate expression in an aggregate query without GROUP BY is evaluated once across the entire dataset.[2] In PostgreSQL, an empty grouping set aggregates all rows down to a single group, which is output even if no input rows were present.[3]

Compared with the alternatives

In DuckDB, the SUMMARIZE command computes aggregates such as min, max, avg, std, q25, q50, q75 and count over all columns, returning them with each column's name, type and percentage of NULLs.[5] In DuckDB, the quantiles and percentiles that SUMMARIZE returns are approximate values.[5] Write the one-row aggregate yourself when you need exact figures or a measure SUMMARIZE does not include, and reach for SUMMARIZE for a first look at a table. (Meridian's view)

Sources

  1. [1]Relational algebra - Wikipediasecondary source
  2. [2]SELECTdocumentation
  3. [3]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation
  4. [4]PostgreSQL: Documentation: 18: 9.21. Aggregate Functionsdocumentation
  5. [5]Summarize – DuckDBdocumentation