Malloy Documentation
search
previousFields
Expressionsnext

Aggregates

VSCode document

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.

document
run: flights -> {
  aggregate: flight_count is count()
}
QUERY RESULTS 
[
  {
    "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.

document
run: order_items -> {
  aggregate: order_count is count(order_id)
}
QUERY RESULTS 
[
  {
    "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.

document
run: flights -> {
  aggregate: total_distance is sum(distance)
}
QUERY RESULTS 
[
  {
    "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.

document
run: aircraft -> {
  aggregate: average_seats is avg(aircraft_models.seats)
}
QUERY RESULTS