Malloy supports the standard aggregate functions count
, sum
, avg
, min
, and max
. When these are used in a field's definition, they make that field a measure.
Basic Syntax
count
count() source.count() join_name.count()
The count
aggregate function may be used to count the number of records appearing in a source.
run: flights -> { aggregate: flight_count is count() }
[ { "flight_count": 344827 } ]
SELECT COUNT(1) as "flight_count" FROM '../data/flights.parquet' as base
count(expr)
count(expression) source.count(expression) join_name.field.count()
Distinct counts may be used to count the number of distinct values of a particular expression within a source.
run: order_items -> { aggregate: order_count is count(order_id) }
[ { "order_count": 264071 } ]
SELECT count(distinct base."order_id") as "order_count" FROM '../data/order_items.parquet' as base
sum
sum(expression) source.sum(expression) join_name.sum(expression) join_name.field.sum()
The sum
function may be used to compute the sum of all records of a particular field.
run: flights -> { aggregate: total_distance is sum(distance) }
[ { "total_distance": 255337195 } ]
SELECT COALESCE(SUM(base."distance"),0) as "total_distance" FROM '../data/flights.parquet' as base
avg
avg(expression) source.avg(expression) join_name.avg(expression) join_name.field.avg()
The avg
function may be used to compute the average of all records of a particular field.