other.malloynb

>>>markdown
# Bucketing with 'Other' 

Often you want to limit the number of group-by values in a table, and bucket everything else into an 'other' category.  

In the `top_states_by_eleveation` query below, we have a query with two stages. The first stage calculates the top states and nests the data to be aggregated. The second pipeline stage produces the actual aggregation.
>>>malloy
source: airports is duckdb.table('../data/airports.parquet') extend {
  measure: 
    airport_count is count()
    avg_elevation is elevation.avg()

  view: top_states_by_elevation is {
    group_by: state
    aggregate: avg_elevation
    calculate: row_num is row_number()
    nest: data is  {  
      group_by: code, elevation
    }
  } -> {
    group_by: state is 
      pick state when row_num < 5
      else 'OTHER'
    aggregate: 
      avg_elevation is data.elevation.avg()
      airport_count is data.count()
  }
}
>>>markdown

## Basic Query
>>>malloy
#(docs) size=large limit=5000
run: airports -> top_states_by_elevation
>>>markdown

## Nested Query
>>>malloy
#(docs) size=large limit=5000
run: airports -> {
  group_by: `Facility Type` is fac_type
  aggregate: 
    airport_count
    avg_elevation
  nest: top_states_by_elevation
}
>>>markdown