Shape / table · All operations

Repeat a group total on every row

A variant of Calculate across rows

Aggregate over the whole partition or table and put the result on every row, for shares of total and comparisons with the group.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Repeat a group total, and the grand total, on every row

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 order_id, product, amount,
       sum(amount) OVER (PARTITION BY product) AS product_total,
       sum(amount) OVER () AS grand_total,
       round(amount / sum(amount) OVER (PARTITION BY product), 4) AS share_of_product
FROM orders;
SELECT * FROM result ORDER BY order_id;
SELECT CASE WHEN ((SELECT count(*) FROM result) = 10 AND (SELECT map_from_entries(list((product, product_total) ORDER BY product)) FROM (SELECT DISTINCT product, product_total FROM result)) = MAP {'gadget': 140.75, 'gizmo': 319.99, 'widget': 1440.00} AND (SELECT count(DISTINCT grand_total) FROM result) = 1 AND (SELECT any_value(grand_total) FROM result) = 1900.74 AND (SELECT share_of_product FROM result WHERE order_id = 1) = 0.0833 AND (SELECT share_of_product FROM result WHERE order_id = 4) IS NULL) THEN 'as expected' ELSE error('the product totals were not 1440.00, 140.75 and 319.99 on every row, or the grand total 1900.74 was not repeated on all ten') 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 product totals were not 1440.00, 140.75 and 319.99 on every row, or the grand total 1900.74 was not repeated on all ten”.
  • 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).

Bruin (1)
Duckle (1)
EasyMorph (2)
  • Distribute totalread 2026-09-27

    Break a total down across rows in proportion to a column of weights

  • Total/subtotalsread 2026-09-27

    Add a column holding the column total, or the subtotal of each row's group

OpenRefine (1)
Polars (1)
  • Expr.overread 2026-09-27

    Evaluate an expression within partitions and map the result back onto every row

Tableau Prep (2)
  • Fixed LODread 2026-09-27

    Aggregate at the level of chosen fields and repeat the result on every row of the group

  • { FIXED }read 2026-09-27

    Level-of-detail expression: aggregate at the named dimensions, repeated on each row

VisiData (1)
  • addcol-aggregateread 2026-09-23

    Add column holding an aggregate over the rows that share the same key values

Background

An aggregate with OVER (PARTITION BY ...) puts the result for each row's whole partition beside the row, such as each employee's department average salary.[1][2] If the PARTITION BY clause is omitted, as in an empty OVER (), the entire result set is treated as a single partition.[1] In DuckDB, amount / sum(amount) OVER (PARTITION BY region) computes each row's percentage of its region's total sales.[3]

When to use it

In PostgreSQL, an aggregate used with ORDER BY and the default window frame produces running-sum behaviour, which may or may not be what is wanted.[4] To aggregate over the whole partition in PostgreSQL, omit ORDER BY or use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.[4] Leave ORDER BY out of a group-total window, or spell the full frame out, so a total cannot quietly turn into a running one. (Meridian's view)

In PostgreSQL, a row removed because it does not meet the WHERE condition is not seen by any window function.[2] Filter before the window when the removed rows should leave the denominator of a share as well, and filter afterwards when they should still count towards the total. (Meridian's view) In PostgreSQL, when FILTER is given on an aggregate window function, only the input rows for which the filter clause is true are fed to it.[5] A FILTER is the tidy way to put a conditional total, such as the group's refunds alone, beside every row of the group. (Meridian's view)

The method

In PostgreSQL, PARTITION BY works much like a query-level GROUP BY, except that its expressions cannot be output-column names or numbers.[5]

Compared with the alternatives

In PostgreSQL, avg with an OVER clause is the same function as the non-window avg aggregate, but the OVER clause has it computed across the window frame.[2] In PostgreSQL, unlike a non-window aggregate, an aggregate used as a window function does not group the rows into a single output row, so each row keeps its own place.[2]

Sources

  1. [1]Window function (SQL) - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: 3.5. Window Functionsdocumentation
  3. [3]Window Functions – DuckDBdocumentation
  4. [4]PostgreSQL: Documentation: 18: 9.22. Window Functionsdocumentation
  5. [5]PostgreSQL: Documentation: 18: 4.2. Value Expressionsdocumentation