Aggregate Functions

Seven functions that turn many rows into one: count, count_distinct, sum, avg, min, max and string_agg. You never write GROUP BY — using one of these in a projection is what makes the query grouped, and the columns you also fetch become the grouping key.

count() and count(x) ask different questions

count() counts rows. count(x) counts the rows where x is not blank, which is how you ask "how many orders actually have a shipping date". The two differ exactly by the blanks, and that is usually the point of writing the second one.

count()                 →  830
count(o.shipped_date)   →  809   -- 21 orders never shipped

count() becomes COUNT(*), not count(). Only DuckDB, SQLite and Trino accept the empty parentheses; PostgreSQL, SQL Server, MariaDB, Oracle and Snowflake reject them outright with "must be used to call a parameterless aggregate function". That the reference dialect is one of the three permissive ones is why it went unnoticed for a while.

count_distinct(x) is its own name rather than a modifier on count, because DISTINCT inside a function call is SQL syntax that not every engine allows in every position — see the window note below.

sum keeps the type, avg does not

sum, min and max return whatever the argument was: a sum of integers is an integer, a sum of money is money. avg always comes back fractional, because an average of whole numbers usually isn't one.

That distinction is worth stating because an engine can disagree: Trino used to return the average of a DECIMAL(10,2) column as DECIMAL(10,2) as well, so the mean of three prices came out 11.92 where every other engine said 11.916667. The average of money is not money, and it is levelled now.

string_agg needs its separator

string_agg(o.ship_city, ', ')

There is no default — the separator is a required argument, because the engines disagree on what a missing one means. Each spells the function differently (GROUP_CONCAT, LISTAGG, array_join(array_agg(…))), which is invisible until you try to use it as a window function.

Not every aggregate can be a window function

string_agg has no OVER form on MariaDB, SQL Server, Oracle, Snowflake or Trino, and count_distinct has none on MariaDB, SQL Server, PostgreSQL or SQLite. DuckDB is the only engine that allows both. Where the engine cannot, the query is refused before it runs, with a message naming the function and pointing at it — not a driver error after the fact.

count

count([value: any]) → BIGINT (aggregate)

Number of input rows, or of non-null values when an expression is given.

Operands: any type.

Sample query:

// count: number of orders.
FIND orders o
FETCH count(o.order_id) order_count

Generated SQL

all dialects

-- count: number of orders.
SELECT
  count(o.order_id) AS order_count
FROM
 orders o

count_distinct

count_distinct(value: any) → BIGINT (aggregate)

Number of distinct non-null values — how many different customers, say, rather than how many rows.

Operands: any type.

Sample query:

// count_distinct: how many different customers ordered from each country — not how many orders,
// which is what plain count would give. The one function every BI tool exposes under its own name.
FIND customers c, orders o
FETCH c.country, count_distinct(c.customer_id) customers, count(o.order_id) orders

Generated SQL

all dialects

-- count_distinct: how many different customers ordered from each country — not how many orders,
-- which is what plain count would give. The one function every BI tool exposes under its own name.
SELECT
  c.country
, COUNT(DISTINCT c.customer_id) AS customers
, count(o.order_id) AS orders
FROM
 customers c
  INNER JOIN orders o ON
   c.customer_id = o.customer_id
GROUP BY
  c.country

avg

avg(value: numeric | duration) → FLOAT (aggregate)

Average of the input values.

Sample query:

// avg: average line-item unit price.
FIND order_details od
FETCH avg(od.unit_price) avg_price

Generated SQL

duckdb · oracle · snowflake · mssql · postgresql · mariadb · sqlite

-- avg: average line-item unit price.
SELECT
  avg(od.unit_price) AS avg_price
FROM
 order_details od

The remaining dialects differ only in this expression:

sum

sum(value: numeric | duration) → argument-dependent (aggregate)

Sum of the input values.

Sample query:

// sum: total quantity ordered across all lines.
FIND order_details od
FETCH sum(od.quantity) total_quantity

Generated SQL

all dialects

-- sum: total quantity ordered across all lines.
SELECT
  sum(od.quantity) AS total_quantity
FROM
 order_details od

min

min(value: any) → argument-dependent (aggregate)

Minimum input value.

Operands: any type.

Sample query:

// min: earliest order date.
FIND orders o
FETCH min(o.order_date) earliest

Generated SQL

all dialects

-- min: earliest order date.
SELECT
  min(o.order_date) AS earliest
FROM
 orders o

max

max(value: any) → argument-dependent (aggregate)

Maximum input value.

Operands: any type.

Sample query:

// max: latest order date.
FIND orders o
FETCH max(o.order_date) latest

Generated SQL

all dialects

-- max: latest order date.
SELECT
  max(o.order_date) AS latest
FROM
 orders o

string_agg

string_agg(value: any, separator: TEXT) → TEXT (aggregate)

Concatenates non-null input values into a string, separated by separator. The order is unspecified: no engine promises one for an aggregate without an explicit sort, so the same query may answer differently on another dialect, another plan, or another run. Pass order_by to fix it.

string_agg(value: any, separator: TEXT, order_by: any) → TEXT (aggregate)

Concatenates non-null input values into a string, separated by separator, in ascending order of order_by. The two-argument form leaves the order to the engine.

Sample query:

// string_agg: the product names of a category, in one cell, in a fixed order.
//
// Sorted by product_id rather than by name on purpose: a text sort depends on the engine's
// collation, so the same names come back in a different order on PostgreSQL and Oracle than on
// DuckDB. An integer sort is the same everywhere.
FIND products p
FETCH p.category_id category, string_agg(p.product_name, ', ', p.product_id) names

Generated SQL

duckdb · postgresql · sqlite

-- string_agg: the product names of a category, in one cell, in a fixed order.
-- Sorted by product_id rather than by name on purpose: a text sort depends on the engine's
-- collation, so the same names come back in a different order on PostgreSQL and Oracle than on
-- DuckDB. An integer sort is the same everywhere.
SELECT
  p.category_id AS category
, string_agg(p.product_name, ', ' ORDER BY p.product_id) AS names
FROM
 products p
GROUP BY
  p.category_id

The remaining dialects differ only in this expression: