Nested Aggregations
Inline Nesting
In the previous examples, we created two metrics: an inner metric using BY
and an outer metric. However, it’s possible to achieve the same result with a single metric.
Instead of:
- Total Category Amount: SELECT SUM(Amount) BY Category
- Average Category Amount: SELECT AVG(Total Category Amount)
We can write:
- Average Category Amount as SELECT AVG(SELECT SUM(Amount) BY Category).
In MAQL syntax:
SELECT AVG(SELECT SUM({fact/amount}) BY {label/category})
.
The BY
keyword always stays with the inner metric to define its aggregation level.
Common Errors
Using BY
in the outer metric
Especially when you use the inline nesting of metrics, it is easy to mix parentheses and put the BY
outside of them.
Mixing up parentheses and placing BY outside can lead to incorrect results. For example, SELECT AVG(SELECT SUM(Amount)) BY Category will return the sum of Amount without slicing by category, resulting in a single number.
Omitting BY
completely
Not using BY
means the inner metric doesn’t know where to stop. SELECT AVG(SELECT SUM(Amount)) returns the same as SELECT SUM(Amount) because the average is calculated from a single number.
More Levels of Nesting
Metrics can be nested multiple times as needed. For example, to find out how many regions have more than 1,000 active users each month, you can first calculate the active users for each month and region, then count regions exceeding 1,000 users, and finally average this count over the months.