Aggregate functions

Aggregate functions combine input rows into one result, or one result per GROUP BY key. See SELECT for grouping and HAVING.

Function	Result
avg	Arithmetic mean.
count	Row count or count of values.
first	One input value.
min and max	Smallest or largest value.
sum	Sum of numeric values.

avg

avg(number)

Accepts a numeric expression and returns its arithmetic mean.

SELECT avg(range) AS mean FROM range(1, 4);
-- mean: 2.0

count

count(*)
count(expression)
count(DISTINCT expression)

Returns a BIGINT count. count(*) counts input rows; count(expression) counts non-NULL values. DISTINCT counts distinct non-NULL values.

SELECT count(*) AS rows, count(DISTINCT region) AS regions
FROM (VALUES ('eu'), ('eu'), ('us')) AS input(region);
-- rows: 3, regions: 2

DISTINCT is not supported by the other aggregates.

first

first(expression)

Returns one input value. Which value is returned is unspecified without an ordering guarantee, so do not use it to choose the earliest or latest row.

SELECT first(range) AS value FROM range(1, 2);
-- value: 1 (the input has just one row)

min and max

min(expression)
max(expression)

Return the minimum or maximum input value. The result follows the expression’s type: cast before aggregating when a different comparison is intended.

SELECT min(range) AS smallest, max(range) AS largest FROM range(1, 4);
-- smallest: 1, largest: 3

sum

sum(number)

Returns the sum of a numeric expression. Integer sums use a HUGEINT result to avoid narrow integer overflow; see numeric types.

SELECT sum(range) AS total FROM range(1, 4);
-- total: 6