Aggregation

Aggregate functions summarize values from many rows.

How count works

In a whole-file summary, count(...) returns the number of source rows, even when its arguments are missing or null.

Function	Result
sum(values...)	Sum
avg(values...)	Arithmetic mean
count(values...)	Number of rows or defined row values
max(values...)	Largest numeric value
min(values...)	Smallest numeric value

Every aggregate function accepts one or more arguments. Numeric strings participate in numeric calculations. Null, missing, and non-numeric values are ignored by sum, avg, max, and min.

Summarize a whole file

Leave out projection braces to return one summary object:

select revenue: sum(amount), average: avg(amount), orders: count(id)
from 'orders.json'

For an empty selection, sum, avg, and count return 0. max and min have no value and are not included in the result.

Calculate across fields in one row

Inside an ordinary object projection, aggregate functions work with their arguments on the current row:

select {total: sum(net, tax), averagePart: avg(net, tax)}
from 'orders.json'

In row mode, count(net, tax) counts arguments that are not null or missing.

Aggregate groups

With group by, aggregate functions calculate a value for every group:

select {region, revenue: sum(amount), orders: count(id)}
from 'orders.json'
group by region

See Grouping for group behavior and having.