Shape / table · All operations

Summarise by group

Group by and summarise.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
a new grain: each row stands for a group of input rows
Level
one clause of SQL

The DuckDB step

Group and summarise

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 region, count(*) AS orders, sum(amount) AS total, round(avg(amount), 2) AS mean, max(order_date) AS latest FROM orders GROUP BY region ORDER BY total DESC;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 5 AND sum(total) = 1900.74 AND max(total) FILTER (WHERE region = 'west') = 1020 AND max(orders) FILTER (WHERE region = 'north') = 3 FROM result)) THEN 'as expected' ELSE error('the groups do not total 1900.74 with west at 1020') 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 groups do not total 1900.74 with west at 1020”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Group rows and aggregate

Collapse the rows sharing a key into one row per key, with sums, counts, averages and other aggregates of the remaining columns.

Other summaries

Operators filed here that summarise in another way, such as run-length encoding or attaching an aggregate to every item.

Pick one value from the group

Return one value from each group: any, the first or last by an ordering, the nth or kth largest, or another column's value at the row where a key is largest or smallest.

Spread and statistical summaries

Compute standard deviation, variance, correlation, covariance, mode, weighted averages and other statistics per group.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (6)
  • Calgary Cross Countread 2026-09-27

    Count records per group across several Calgary database fields

  • Count Recordsread 2026-09-27

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

  • Create Profileread 2026-09-27

    Build a behaviour profile, a distribution over segments, from a data stream or geography

  • Summarizeread 2026-09-27

    Group by and aggregate: sum, count, min, max, concatenate, spatial and statistical actions

  • Summarize In-DBread 2026-09-27

    Group, sum, count and count distinct inside the database

  • Weighted Averageread 2026-09-27

    Compute the weighted average of a field, optionally per group

Apache DataFusion (33)
  • AGGREGATEread 2026-09-27

    Pipe: aggregate, optionally grouped, BigQuery-style |> syntax

  • Aggregate (GROUP BY)read 2026-09-27

    Group rows and compute aggregate expressions per group

  • FILTER (WHERE ...)read 2026-09-27

    Restrict which input rows contribute to an aggregate

  • GROUP BY ALLread 2026-09-27

    Group by every non-aggregate expression in the SELECT list

  • WITHIN GROUP (ORDER BY ...)read 2026-09-27

    Give ordered-set aggregates the ordering they rely on

  • aggregateread 2026-09-27

    DataFrame method: Group by expressions and compute aggregates

  • any_valueread 2026-09-27

    Aggregate: Arbitrary non-null value from the group

  • approx_distinctread 2026-09-27

    Aggregate: Approximate distinct count by HyperLogLog

  • approx_medianread 2026-09-27

    Aggregate: Approximate median

  • approx_percentile_contread 2026-09-27

    Aggregate: Approximate percentile by t-digest

  • approx_percentile_cont_with_weightread 2026-09-27

    Aggregate: Weighted approximate percentile by t-digest

  • array_aggread 2026-09-27

    Aggregate: Array of the group values, optionally ordered or distinct

  • avgread 2026-09-27

    Aggregate: Average of numeric values; alias mean

  • bit_andread 2026-09-27

    Aggregate: Bitwise AND of non-null values

  • bit_orread 2026-09-27

    Aggregate: Bitwise OR of non-null values

  • bit_xorread 2026-09-27

    Aggregate: Bitwise exclusive OR of non-null values

  • bool_andread 2026-09-27

    Aggregate: True when all non-null values are true

  • bool_orread 2026-09-27

    Aggregate: True when any non-null value is true

  • countread 2026-09-27

    Aggregate: Number of non-null values, or of rows with count(*)

  • countread 2026-09-27

    DataFrame method: Return the total number of rows

  • first_valueread 2026-09-27

    Aggregate: First value in the group by the requested ordering

  • last_valueread 2026-09-27

    Aggregate: Last value in the group by the requested ordering

  • maxread 2026-09-27

    Aggregate: Maximum value

  • medianread 2026-09-27

    Aggregate: Median value

  • minread 2026-09-27

    Aggregate: Minimum value

  • nth_valueread 2026-09-27

    Aggregate: Nth value in a group of values

  • percentile_contread 2026-09-27

    Aggregate: Exact percentile with interpolation; alias quantile_cont

  • stddevread 2026-09-27

    Aggregate: Sample standard deviation; alias stddev_samp

  • stddev_popread 2026-09-27

    Aggregate: Population standard deviation

  • string_aggread 2026-09-27

    Aggregate: Concatenate strings with a separator, optionally ordered

  • sumread 2026-09-27

    Aggregate: Sum of values

  • varread 2026-09-27

    Aggregate: Sample variance; aliases var_samp, var_sample

  • var_popread 2026-09-27

    Aggregate: Population variance; alias var_population

Apache Hop (2)
  • Group Byread 2026-09-27

    Aggregate sorted rows by group with sum, average, count, percentile, concatenate and more

  • Memory Group Byread 2026-09-27

    Aggregate unsorted rows by group in memory

AWS Glue (1)
  • Aggregateread 2026-09-27

    Compute average, sum, min, max and the like over chosen fields, grouped by fields

Azure Data Factory (3)
  • Aggregateread 2026-09-27

    Group by existing or computed columns and compute aggregates such as sum, min, max, count

  • Aggregate functionsread 2026-09-27

    44 functions: approxDistinctCount, avg, avgIf, collect, collectUnique, …

  • Group byread 2026-09-27

    Table.Group with sum, count, average, min, max, standard deviation, first, last

Bruin (2)
  • Metricsread 2026-09-27

    Named aggregations, derived metrics by reference, and optional per-metric SQL filters

  • bruin.group_by(n)read 2026-09-27

    Emit a positional GROUP BY 1..n clause

dbt-utils (4)
  • any_valueread 2026-09-27

    Aggregate returning any value of the expression from the group

  • bool_orread 2026-09-27

    Aggregate returning true when any value in the group is true

  • group_byread 2026-09-27

    Emit a positional GROUP BY 1..N clause

  • listaggread 2026-09-27

    Aggregate concatenating group values into one delimited string, with order and limit

DuckDB (51)
  • Aggregate Functions (family)read 2026-09-27

    The functions documented on the Aggregate Functions page

  • FILTER (clause)read 2026-09-27

    Restrict the rows one aggregate sees

  • GROUP BY (clause)read 2026-09-27

    Group rows and aggregate; GROUP BY ALL groups by every column not aggregated

  • any_valueread 2026-09-27

    Returns the first non-NULL value from arg. This function is affected by ordering.

  • approx_count_distinctread 2026-09-27

    Computes the approximate count of distinct elements using HyperLogLog.

  • arbitraryread 2026-09-27

    Returns the first value (NULL or non-NULL) from arg. This function is affected by ordering.

  • arg_maxread 2026-09-27

    Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.

  • arg_max_nullread 2026-09-27

    Finds the row with the maximum val. Calculates the arg expression at that row.

  • arg_max_nulls_lastread 2026-09-27

    Finds the rows with N maximum vals, including nulls. Calculates the arg expression at that row.

  • arg_minread 2026-09-27

    Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.

  • arg_min_nullread 2026-09-27

    Finds the row with the minimum val. Calculates the arg expression at that row.

  • arg_min_nulls_lastread 2026-09-27

    Finds the rows with N minimum vals, including nulls. Calculates the arg expression at that row.

  • argmaxread 2026-09-27

    Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.

  • argminread 2026-09-27

    Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.

  • array_aggread 2026-09-27

    Returns a LIST containing all the values of a column.

  • avgread 2026-09-27

    Calculates the average value for all tuples in x.

  • bit_andread 2026-09-27

    Returns the bitwise AND of all bits in a given expression.

  • bit_orread 2026-09-27

    Returns the bitwise OR of all bits in a given expression.

  • bit_xorread 2026-09-27

    Returns the bitwise XOR of all bits in a given expression.

  • bitstring_aggread 2026-09-27

    Returns a bitstring with bits set for each distinct value.

  • bool_andread 2026-09-27

    Returns TRUE if every input value is TRUE, otherwise FALSE.

  • bool_orread 2026-09-27

    Returns TRUE if any input value is TRUE, otherwise FALSE.

  • countread 2026-09-27

    Returns the number of non-NULL values in arg.

  • count_ifread 2026-09-27

    Counts the total number of TRUE values for a boolean column

  • count_starread 2026-09-27

    count_star aggregate

  • countifread 2026-09-27

    Counts the total number of TRUE values for a boolean column

  • favgread 2026-09-27

    Calculates the average using a more accurate floating point summation (Kahan Sum)

  • firstread 2026-09-27

    Returns the first value (NULL or non-NULL) from arg. This function is affected by ordering.

  • fsumread 2026-09-27

    Calculates the sum using a more accurate floating point summation (Kahan Sum).

  • group_concatread 2026-09-27

    Concatenates the column string values with an optional separator.

  • kahan_sumread 2026-09-27

    Calculates the sum using a more accurate floating point summation (Kahan Sum).

  • lastread 2026-09-27

    Returns the last value of a column. This function is affected by ordering.

  • listread 2026-09-27

    Returns a LIST containing all the values of a column.

  • listaggread 2026-09-27

    Concatenates the column string values with an optional separator.

  • maxread 2026-09-27

    Returns the maximum value present in arg.

  • max_byread 2026-09-27

    Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.

  • meanread 2026-09-27

    Calculates the average value for all tuples in x.

  • minread 2026-09-27

    Returns the minimum value present in arg.

  • min_byread 2026-09-27

    Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.

  • productread 2026-09-27

    Calculates the product of all tuples in arg.

  • semread 2026-09-27

    Returns the standard error of the mean

  • stddevread 2026-09-27

    Returns the sample standard deviation

  • stddev_popread 2026-09-27

    Returns the population standard deviation.

  • stddev_sampread 2026-09-27

    Returns the sample standard deviation

  • string_aggread 2026-09-27

    Concatenates the column string values with an optional separator.

  • sumread 2026-09-27

    Calculates the sum value for all tuples in arg.

  • sum_no_overflowread 2026-09-27

    Internal only. Calculates the sum value for all tuples in arg without overflow checks.

  • sumkahanread 2026-09-27

    Calculates the sum using a more accurate floating point summation (Kahan Sum).

  • var_popread 2026-09-27

    Returns the population variance.

  • var_sampread 2026-09-27

    Returns the sample variance of all input values.

  • varianceread 2026-09-27

    Returns the sample variance of all input values.

Duckle (7)
  • Approx Quantileread 2026-09-25

    approximate quantile per group with approx_quantile

  • Array Aggregateread 2026-09-25

    collapse rows into a JSON array per group

  • Collect Listread 2026-09-25

    collect values into a list per group

  • Count Rowsread 2026-09-25

    count rows

  • Denormalizeread 2026-09-25

    collapse rows per group, joining values into delimited cells

  • Diff Summaryread 2026-09-25

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

  • Group Byread 2026-09-25

    group by and aggregate

EasyMorph (3)
  • Aggregateread 2026-09-27

    Group by one or more columns and compute sums, counts and other aggregates

  • Concatenate textread 2026-09-27

    Join a column's values across rows, optionally grouped, or across columns, into delimited text

  • Statistical aggregationread 2026-09-27

    Compute median, percentile, standard deviation, standard error or variance

Ibis (34)
  • Aggregateread 2026-09-27

    Group a table by key columns and compute metrics per group

  • Allread 2026-09-27

    Test whether every value of a boolean column is true

  • Anyread 2026-09-27

    Test whether any value of a boolean column is true

  • ApproxCountDistinctread 2026-09-27

    Approximate count of the distinct values of a column

  • ApproxMedianread 2026-09-27

    Approximate median of a column

  • ApproxMultiQuantileread 2026-09-27

    Approximations of several quantiles of a column, as an array

  • ApproxQuantileread 2026-09-27

    Approximation of a chosen quantile of a column

  • Arbitraryread 2026-09-27

    Pick any one non-null value of a column

  • ArgMaxread 2026-09-27

    Value of one column at the row where a key column is largest

  • ArgMinread 2026-09-27

    Value of one column at the row where a key column is smallest

  • ArrayCollectread 2026-09-27

    Collect the values of a column into one array

  • BitAndread 2026-09-27

    Bitwise AND across the values of a column

  • BitOrread 2026-09-27

    Bitwise OR across the values of a column

  • BitXorread 2026-09-27

    Bitwise XOR across the values of a column

  • Correlationread 2026-09-27

    Correlation coefficient of two columns

  • Countread 2026-09-27

    Count the non-null values of a column

  • CountDistinctread 2026-09-27

    Count the distinct values of a column

  • CountDistinctStarread 2026-09-27

    Count the distinct rows of a relation

  • CountStarread 2026-09-27

    Count the rows of a relation

  • Covarianceread 2026-09-27

    Covariance of two columns

  • Firstread 2026-09-27

    First value of a column, with optional ordering and filter

  • GroupConcatread 2026-09-27

    Concatenate the strings of a group with a separator

  • Kurtosisread 2026-09-27

    Kurtosis of a column, sample or population

  • Lastread 2026-09-27

    Last value of a column, with optional ordering and filter

  • Maxread 2026-09-27

    Largest value of a column

  • Meanread 2026-09-27

    Mean of a column

  • Medianread 2026-09-27

    Median of a column

  • Minread 2026-09-27

    Smallest value of a column

  • Moderead 2026-09-27

    Most frequent value of a column

  • MultiQuantileread 2026-09-27

    Several quantiles of a column, returned as an array

  • Quantileread 2026-09-27

    A chosen quantile of a column

  • StandardDevread 2026-09-27

    Standard deviation of a column, sample or population

  • Sumread 2026-09-27

    Sum of a column

  • Varianceread 2026-09-27

    Variance of a column, sample or population

KNIME Analytics Platform (3)
  • DB GroupByread 2026-09-27

    In-database: group by selected columns and aggregate the rest as SQL

  • GroupByread 2026-09-27

    Group rows by selected columns and aggregate the remaining columns

  • Row Aggregatorread 2026-09-27

    Aggregate columns with one function, optionally grouped by a category and weighted

OpenRefine (1)
Pentaho Data Integration (2)
  • Group Byread 2026-09-27

    Aggregate sorted rows by group

  • Memory Group Byread 2026-09-27

    Aggregate rows by group in memory, without needing sorted input

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

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

  • DataFrame.group_byread 2026-09-27

    Group rows by key columns or expressions, ready for aggregation

  • 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

  • Expr aggregation expressions (group)read 2026-09-27

    31 expressions: sum, mean, median, quantile, n_unique and others; reduce a column, or each group of it, to one value

  • Expr computation: statistics (group)read 2026-09-27

    7 expressions: entropy, kurtosis, skew, mode, n_unique, approx_n_unique, dot; summarise a column to a statistic

  • Expr.rleread 2026-09-27

    Run-length encode a column into structs of run length and value

  • Expr.str: vertical concatenation (group)read 2026-09-27

    2 expressions: join, concat; join the strings of a column into one string, concat being deprecated for join

  • Functions: aggregation shortcuts (group)read 2026-09-27

    17 functions: sum, mean, max, n_unique, quantile and others; shorthand for the same aggregation on named columns

  • GroupBy.aggread 2026-09-27

    Compute aggregation expressions for every group

  • GroupBy.allread 2026-09-27

    Collect the values of every group into list columns

  • GroupBy.countread 2026-09-27

    Count the rows of every group; renamed to len

  • GroupBy.firstread 2026-09-27

    First value of every column per group

  • GroupBy.lastread 2026-09-27

    Last value of every column per group

  • GroupBy.lenread 2026-09-27

    Count the rows of every group

  • GroupBy.maxread 2026-09-27

    Maximum of every column per group

  • GroupBy.meanread 2026-09-27

    Mean of every column per group

  • GroupBy.medianread 2026-09-27

    Median of every column per group

  • GroupBy.minread 2026-09-27

    Minimum of every column per group

  • GroupBy.n_uniqueread 2026-09-27

    Distinct count of every column per group

  • GroupBy.quantileread 2026-09-27

    A chosen quantile of every column per group

  • GroupBy.sumread 2026-09-27

    Sum of every column per group

  • SQL functions: Aggregate (group)read 2026-09-27

    16 functions: AVG, CORR, COUNT, COVAR, FIRST and others; SQL aggregate functions

  • corrread 2026-09-27

    Pearson or Spearman rank correlation between two columns

  • covread 2026-09-27

    Covariance between two columns

Power Query M (2)
  • Table.AggregateTableColumnread 2026-09-27

    Summarise a column of nested tables into new aggregate columns on the outer table

  • Table.Groupread 2026-09-27

    Group rows by key columns and compute aggregate columns per group

RapidMiner Studio (5)
  • Aggregateread 2026-09-27

    Group by columns and compute SQL-style aggregates

  • Aggregate (In Database)read 2026-09-27

    Group by and aggregate, executed as SQL in the database

  • Aggregate Meta Dataread 2026-09-27

    Aggregate a numeric metadata field across documents and attach the result to each

  • 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

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

    Include a GROUP BY clause only when the argument is true

Tableau Prep (2)
  • Aggregateread 2026-09-27

    Group by chosen fields and aggregate the rest to change the level of detail

  • Aggregate functions (group)read 2026-09-27

    11 functions: AVG, COUNTD, MEDIAN, PERCENTILE, STDEV; valid inside a level-of-detail expression

Talend (7)
  • tAggregateRowread 2026-09-27

    Group rows on one or more columns and compute aggregates such as min, max and sum

  • tAggregateSortedRowread 2026-09-27

    Group and aggregate input that is already sorted

  • tCombinedSQLAggregateread 2026-09-27

    Group and aggregate columns of a table inside the DBMS, as part of a combined SQL statement

  • tDenormalizeread 2026-09-27

    Concatenate the values of a column across each group of rows into one delimited value

  • tDenormalizeSortedRowread 2026-09-27

    Combine each group of sorted rows into one row, joining distinct values with a separator

  • tSQLTemplateAggregateread 2026-09-27

    Group and aggregate columns of a table with an SQL template run in the DBMS

  • tSurviveFieldsread 2026-09-27

    Merge rows sharing a key into one master row, with an operation per column such as min, max or sum

Trifacta (43)
VisiData (6)
  • addcol-aggrread 2026-09-23

    On a pivot or frequency table, add another aggregation of the current column's source

  • aggregate-colread 2026-09-23

    Attach an aggregator (min, max, sum, mean, median, count, distinct, list) to current column

  • aggregate-colsread 2026-09-23

    Attach aggregators to the selected source columns from the Columns Sheet

  • flowsread 2026-09-23

    (pcap) Open flows sheet derived from the packet sheet

  • 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 SQL, a GROUP BY clause partitions rows into groups by their values in one or more columns, and the result holds one row for each group.[1] An aggregate function processes multiple values together to form a single summary statistic, such as an average, a count, a maximum, a minimum or a sum.[2] Grouping is typically used to apply an aggregate function to each group.[1]

When to use it

In PostgreSQL, a column not listed in GROUP BY generally cannot be referenced in a grouped query except inside an aggregate expression.[3] In SQLite, a bare column in a grouped query, one neither aggregated nor grouped, takes its value from one of the group's input rows, and you usually do not know which.[4] Put every column you want to show either in the GROUP BY or inside an aggregate, so no value in a group's row depends on which input row the engine happened to read. (Meridian's view) DuckDB's GROUP BY ALL groups by every column in the SELECT that is not wrapped in an aggregate function, which keeps the SELECT granularity aligned with the GROUP BY.[5]

In PostgreSQL, most aggregate functions ignore null inputs, so count(*) counts every input row while count(f1) counts only the rows in which f1 is not null.[6] In SQLite, NULL values are considered equal for the purposes of grouping rows.[4] Look at a group whose key is NULL before reporting it, since it may gather rows that are missing their key rather than rows that share one. (Meridian's view)

In DuckDB, first, last and list are order-sensitive aggregates that can give non-deterministic results without an ordering, and ordering their arguments makes them deterministic.[7] Whenever a summary picks or strings together values rather than counting or adding them, give the aggregate its own ORDER BY. (Meridian's view)

The method

In relational algebra, the aggregation operation names grouping attributes, which function like a GROUP BY clause in SQL, followed by aggregate functions applied to individual attributes.[8] Five aggregate functions are included with most relational database systems: Sum, Count, Average, Maximum and Minimum.[8]

DuckDB computes grouped aggregates with a hash table rather than by sorting the input first, since adding n rows to a hash table costs O(n) against O(n log n) for sorting.[9] In DuckDB, the aggregate hash table has at most as many entries as there are groups, which can be dramatically fewer than the input rows.[9]

Some aggregate functions, such as COUNT, MAX, MIN and SUM, can be computed for subsets of the data and then aggregated again, which lets the work be divided and run in parallel.[2] An average can be computed in pieces by tracking the sum and the count of each subset and dividing at the end.[2] Aggregates such as DISTINCT COUNT, MEDIAN and MODE cannot be computed without analysing the entire set at once, though in some cases approximations can be distributed.[2]

Compared with the alternatives

In SQL, a window function returns a value for each row, in contrast with an aggregate function, which returns a single value for multiple rows.[10] Reach for a window function when every row should keep its own place and carry its group's figure beside it, and for grouping when one row per group is the answer. (Meridian's view) In SQL, WHERE filters the rows read from the tables before they are grouped, while HAVING is applied after aggregation and filters the grouped rows.[11] In PostgreSQL, grouping without aggregate expressions effectively calculates the set of distinct values in a column, which the DISTINCT clause can also do.[3] In DuckDB, the GROUPING SETS, CUBE and ROLLUP clauses group the same rows along several dimensions in one query.[5]

Sources

  1. [1]Group by (SQL) - Wikipediasecondary source
  2. [2]Aggregate function - Wikipediasecondary source
  3. [3]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation
  4. [4]SELECTdocumentation
  5. [5]GROUP BY Clause – DuckDBdocumentation
  6. [6]PostgreSQL: Documentation: 18: 4.2. Value Expressionsdocumentation
  7. [7]Aggregate Functions – DuckDBdocumentation
  8. [8]Relational algebra - Wikipediasecondary source
  9. [9]Parallel Grouped Aggregation in DuckDB – DuckDBdocumentation
  10. [10]Window function (SQL) - Wikipediasecondary source
  11. [11]Having (SQL) - Wikipediasecondary source

Used in

4 Protocols use this operation: