foreign_sums.malloynb

>>>markdown
# Foreign Sums
Malloy allows you to compute sums and averages correctly based on your join tree. Fan-outs based on join relationships will never impact the correctness of these aggregations. This example has `flights`, joining to `aircraft`, joining to `aircraft_model`.
`aircraft_model` has the number of seats specified on this model of aircraft.  Code below computes sums and averages at various places in the join tree.
>>>malloy
source: aircraft_models is duckdb.table('../data/aircraft_models.parquet') extend {
  primary_key: aircraft_model_code
}

source: aircraft is duckdb.table('../data/aircraft.parquet') extend {
  primary_key: tail_num
  join_one: aircraft_models with aircraft_model_code
}

source: flights is duckdb.table('../data/flights.parquet') extend {
  join_one: aircraft with tail_num
}
>>>malloy

run: flights -> {
  where: dep_time = @2003-01
  group_by: carrier
  aggregate:
    // number of flights
    flight_count is count()
    // number of planes
    aircraft_count is aircraft.count()
    // number of different aircraft_models
    aircraft_model_count is aircraft.aircraft_models.count()
    // count each seat once for each flight.
    seats_for_sale is source.sum(aircraft.aircraft_models.seats)
    // count the seat once for each plane
    seats_on_all_planes is aircraft.sum(aircraft.aircraft_models.seats)
    // average number of seats on each model by model
    average_seats_per_model is aircraft.aircraft_models.seats.avg()
}
>>>markdown