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.