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:

1. A sum of sales, grouped by customer

2. 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.