malloy logoMalloy Documentation

What is an "Aggregating Subquery?"

Aggregating Subqueries are queries nested in other queries. A nested query produces a subtable per row in the query in which it is embedded. In Malloy, queries can be named and referenced in other queries. The technical term of "Aggregating Subquery" is a bit of a mouthful, so we more often refer to it as "nesting a query," or a "nested subtable."

An Aggregating Subquery utilizes a Named Query, which might look like this when defined in the model, or within a query:

  airports_by_facility is (reduce
    fac_type
    airport_count
    )

When a Named Query is nested inside of another query, this forms a nested subtable.

explore airports | reduce
  state
  airport_count
  by_facility is (reduce
    fac_type
    airport_count
  )
state airport_​count by_​facility
TX 1,845
fac_​type airport_​count
AIRPORT 1,389
HELIPORT 435
ULTRALIGHT 8
STOLPORT 8
GLIDERPORT 5
CA 984
fac_​type airport_​count
AIRPORT 569
HELIPORT 396
SEAPLANE BASE 12
GLIDERPORT 3
ULTRALIGHT 2
STOLPORT 2
IL 890
fac_​type airport_​count
AIRPORT 625
HELIPORT 245
SEAPLANE BASE 8
ULTRALIGHT 6
BALLOONPORT 2
GLIDERPORT 2
STOLPORT 2
FL 856
fac_​type airport_​count
AIRPORT 511
HELIPORT 280
SEAPLANE BASE 43
STOLPORT 13
ULTRALIGHT 5
GLIDERPORT 4
PA 804
fac_​type airport_​count
AIRPORT 468
HELIPORT 307
ULTRALIGHT 13
SEAPLANE BASE 10
STOLPORT 3
GLIDERPORT 3

This named query can additionally be used to build out other computations, for example:

  airports_in_ca is airports_by_facility [ state : 'CA' ]

Nesting Nested Queries

Aggregating subqueries can be nested infinitely

explore airports
| reduce
  state
  airport_count
  top_5_counties is (reduce top 5
    county
    airport_count
    by_facility is (reduce
      fac_type
      airport_count
    )
  )
state airport_​count top_​5_​counties
TX 1,845
county airport_​count by_​facility
HARRIS 135
fac_​type airport_​count
HELIPORT 110
AIRPORT 25
TARRANT 63
fac_​type airport_​count
HELIPORT 35
AIRPORT 26
ULTRALIGHT 1
STOLPORT 1
DENTON 53
fac_​type airport_​count
AIRPORT 47
HELIPORT 6
DALLAS 42
fac_​type airport_​count
HELIPORT 32
AIRPORT 9
STOLPORT 1
BEXAR 40
fac_​type airport_​count
AIRPORT 24
HELIPORT 16
CA 984
county airport_​count by_​facility
LOS ANGELES 176
fac_​type airport_​count
HELIPORT 151
AIRPORT 23
SEAPLANE BASE 2
SAN BERNARDINO 71
fac_​type airport_​count
AIRPORT 47
HELIPORT 24
ORANGE 53
fac_​type airport_​count
HELIPORT 47
AIRPORT 6
SAN DIEGO 49
fac_​type airport_​count
AIRPORT 30
HELIPORT 17
GLIDERPORT 2
KERN 49
fac_​type airport_​count
AIRPORT 41
HELIPORT 7
ULTRALIGHT 1
IL 890
county airport_​count by_​facility
COOK 51
fac_​type airport_​count
HELIPORT 44
AIRPORT 7
LA SALLE 39
fac_​type airport_​count
AIRPORT 35
HELIPORT 4
MC HENRY 29
fac_​type airport_​count
AIRPORT 20
HELIPORT 7
SEAPLANE BASE 2
DE KALB 27
fac_​type airport_​count
AIRPORT 24
HELIPORT 3
WINNEBAGO 24
fac_​type airport_​count
AIRPORT 15
HELIPORT 9
FL 856
county airport_​count by_​facility
PALM BEACH 45
fac_​type airport_​count
HELIPORT 30
AIRPORT 14
GLIDERPORT 1
DADE 44
fac_​type airport_​count
HELIPORT 27
AIRPORT 12
GLIDERPORT 2
SEAPLANE BASE 2
STOLPORT 1
POLK 43
fac_​type airport_​count
AIRPORT 18
HELIPORT 16
SEAPLANE BASE 9
MARION 37
fac_​type airport_​count
AIRPORT 27
HELIPORT 7
SEAPLANE BASE 2
STOLPORT 1
ORANGE 36
fac_​type airport_​count
HELIPORT 24
AIRPORT 8
SEAPLANE BASE 4
PA 804
county airport_​count by_​facility
BUCKS 55
fac_​type airport_​count
AIRPORT 32
HELIPORT 19
ULTRALIGHT 2
GLIDERPORT 1
STOLPORT 1
MONTGOMERY 44
fac_​type airport_​count
HELIPORT 29
AIRPORT 14
SEAPLANE BASE 1
ALLEGHENY 31
fac_​type airport_​count
HELIPORT 22
AIRPORT 8
SEAPLANE BASE 1
CHESTER 27
fac_​type airport_​count
HELIPORT 16
AIRPORT 11
PHILADELPHIA 26
fac_​type airport_​count
HELIPORT 22
AIRPORT 4

Filter

Filters can be applied at any level within nested queries.

explore airports
| reduce : [state : 'CA'|'NY'|'MN']
  state
  airport_count
  top_5_counties is (reduce top 5
    county
    airport_count
    major_facilities is (reduce : [major:'Y']
      name is concat(code, ' - ', full_name)
    )
    by_facility is (reduce
      fac_type
      airport_count
    )
  )
state airport_​count top_​5_​counties
CA 984
county airport_​count major_​facilities by_​facility
LOS ANGELES 176
name
BUR - BURBANK-GLENDALE-PASADENA
LAX - LOS ANGELES INTL
LGB - LONG BEACH /DAUGHERTY FIELD/
fac_​type airport_​count
HELIPORT 151
AIRPORT 23
SEAPLANE BASE 2
SAN BERNARDINO 71
name
ONT - ONTARIO INTL
fac_​type airport_​count
AIRPORT 47
HELIPORT 24
ORANGE 53
name
SNA - JOHN WAYNE AIRPORT-ORANGE COUNTY
fac_​type airport_​count
HELIPORT 47
AIRPORT 6
KERN 49
name
BFL - MEADOWS FIELD
IYK - INYOKERN
fac_​type airport_​count
AIRPORT 41
HELIPORT 7
ULTRALIGHT 1
SAN DIEGO 49
name
SAN - SAN DIEGO INTL-LINDBERGH FLD
fac_​type airport_​count
AIRPORT 30
HELIPORT 17
GLIDERPORT 2
NY 576
county airport_​count major_​facilities by_​facility
SUFFOLK 34
name
ISP - LONG ISLAND MAC ARTHUR
fac_​type airport_​count
HELIPORT 18
AIRPORT 15
SEAPLANE BASE 1
ERIE 26
name
BUF - BUFFALO NIAGARA INTL
fac_​type airport_​count
AIRPORT 18
HELIPORT 8
NIAGARA 20
name
fac_​type airport_​count
AIRPORT 15
HELIPORT 5
DUTCHESS 20
name
fac_​type airport_​count
HELIPORT 12
AIRPORT 8
ONONDAGA 18
name
SYR - SYRACUSE HANCOCK INTL
fac_​type airport_​count
AIRPORT 13
HELIPORT 4
SEAPLANE BASE 1
MN 507
county airport_​count major_​facilities by_​facility
ST LOUIS 28
name
DLH - DULUTH INTL
fac_​type airport_​count
AIRPORT 13
SEAPLANE BASE 10
HELIPORT 5
HENNEPIN 23
name
MSP - MINNEAPOLIS-ST PAUL INTL/WOLD-CHAMBERLAIN/
fac_​type airport_​count
HELIPORT 9
SEAPLANE BASE 8
AIRPORT 6
DAKOTA 17
name
fac_​type airport_​count
AIRPORT 14
SEAPLANE BASE 3
CROW WING 17
name
fac_​type airport_​count
AIRPORT 8
SEAPLANE BASE 6
ULTRALIGHT 1
STOLPORT 1
HELIPORT 1
ANOKA 14
name
fac_​type airport_​count
AIRPORT 9
SEAPLANE BASE 3
HELIPORT 2