The Error
While developing in a project, you might see an error like this in an Explore or in the LookML Validator:
So what does it mean?
This error is caused by an aggregate measure referencing another aggregation or measure of any type in its LookML definition. This generates a nested aggregation in SQL:
SELECT AVG((COUNT(*)) ) AS users.average_count FROM demo_db.users AS users
Most SQL dialects are unable to double aggregate, or nest aggregations, so such an attempt triggers the error.
Solutions
There are two possible solutions — use non-aggregate measures, or use a derived table for nested aggregations.
Using Non-Aggregate Measures
Non-aggregate measures, such as type: yesno
and type: number
, are the only measures that can reference other measures or aggregations. Non-aggregate measures do not perform any aggregation and thus will not cause a nested aggregation. Measures of type: number
or type: yesno
act as placeholders so other measures or combinations of measures can be referenced within them.
For example, measures of type: number
are used to perform calculations between measures and take any valid SQL expression that results in a number or an integer.
measure: order_count { # Base measure #1 type: count sql: ${order_id} ;; } measure: cancelled_orders { # Base measure #2 type: count filters: [status: "Cancelled"] } measure: percent_cancelled_orders { # New measure type: number sql: (1.0*${cancelled_orders})/ISNULL(${order_count},0) ;; }
Here, a type: number
measure is used to calculate the percentage of all orders that are cancelled.
Using a Derived Table for Nested Aggregations
But what if a nested aggregation is necessary for performing an analysis? For example, what if you need to know the average amount that customers spend in their customer lifetime ("Average Customer Lifetime Value")? This requires two levels of aggregation:
-
A sum of sales, grouped by customer
-
An average of that sum
To achieve this with LookML, you might try:
measure: total_revenue { type: sum sql: ${sale_price} ;; } measure: avg_customer_lifetime_value { type: average sql: ${total_revenue} ;; }
However, this will trigger the error because the avg_customer_lifetime_value
measure is performing an aggregation on the total_revenue
measure, which is already an aggregation. As discussed above, most SQL dialects will trigger an error when nested aggregates are used in a query.
To achieve an average of the total_revenue
sum in SQL, we need a subquery:
SELECT AVG(s.sum) FROM (SELECT SUM(x) as sum FROM ...) AS s
The equivalent solution in Looker is to create a derived table to "flatten" the total_lifetime_value
measure into a field that can be aggregated. With a derived table, the total_lifetime_value
measure becomes a dimension. We can then create a measure of type: average
that references the customer_lifetime_value
dimension:
view: customer_facts { derived_table: { sql: SELECT user_id, COALESCE(SUM(sale_price), 0) AS customer_lifetime_value FROM orders GROUP BY user_id;; } dimension: customer_lifetime_value { type: number sql: ${TABLE}."customer_lifetime_value" ;; } measure: average_customer_lifetime_value { type: average sql: ${customer_lifetime_value} ;; } }
Once the customer_facts
derived table is joined into an Explore, the average_customer_lifetime_value
measure can be used to perform the desired analysis without triggering an error.