View the original community article here
Last Tested: Mar 16, 2019
The Error:
You might see this error in the LookML validator or the explore.
There is a related error from the same issue: "Error: aggregate function calls may not have nested aggregate or window function".
What does this error mean?
This error is because of a SQL limitation: nested aggregation. Most SQL dialects cannot do the following:
SELECT user_id, AVG(SUM(sale_price)) FROM orders GROUP BY user_id
Common Causes / Fixes:
1.It's fine to have a measure that references another measure, but it must be type: number. That's because the original measure(s) that the new measure is based off is/are already doing an aggregation. Declaring a second aggregate function will cause nested aggregation. Instead, using type: number
acts as a place holder so you can reference other measures or combinations of measures. Example:|
measure: order_count { # Base measure #1
type: count
sql: ${order_id} ;;
}
measure: cancelled_orders { # Base measure #2
type: count
filters: {
field: status
value: "Cancelled"
}
}
measure: percent_cancelled_orders { # New measure
type: number
sql: (1.0*${cancelled_orders})/ISNULL(${order_count},0) ;;
}
2.Attempting multiple levels of aggregation that require a derived table.
Example: say you want to know the average amount that customers spend in their customer lifetime, IE "Average Customer Lifetime Value". This requires two levels of aggregation:
- Sum(sales) group by customer.
- Average of that.
You might try:
measure: total_revenue {
type: sum
sql: ${sale_price} ;;
}
measure: avg_customer_lifetime_value {
type: average
sql: ${total_revenue} ;;
}
But this won't work because:
- Nested aggregation: SQL will err from:
SELECT AVG(SUM(x) FROM ...
, and would require a subquery instead:SELECT AVG(s.sum) FROM (SELECT SUM(x) as sum FROM ...) s
- Measures are designed to be grouped dynamically by any dimension on the fly. In this example, customer_lifetime_value should always, by definition, be group by customer_id. Locking a measure's "group by" requires a derived table.
Solution: create a derived table so the measure total_lifetime_value becomes a dimension, then average it in a measure.
view: customer_facts {
derived_table: {
sql: SELECT user_id, COALESCE(SUM(sale_price), 0) AS customer_lifetime_value
FROM orders ;;
}
dimension: customer_lifetime_value {
type: number
sql: ${TABLE}."customer_lifetime_value" ;;
}
measure: average_customer_lifetime_value {
type: average
sql: ${customer_lifetime_value}
}
}
This content is subject to limited support.