home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
sql_distinct_key

Usage

view: view_name {
  measure: field_name {
    type: sum_distinct
    sql_distinct_key: ${my_field_name} ;;
  }
}

Hierarchy

sql_distinct_key

Possible Field Types

Measure

Accepts

A SQL expression

Definition

The sql_distinct_key parameter is used with measure types that perform aggregations on non-repeated values, specifically measures of type average_distinct, median_distinct, percentile_distinct, and sum_distinct. sql_distinct_key tells Looker which field to use as a basis for determining unique values, thereby avoiding miscalculations in the case of a fanout.

For example, type: sum_distinct adds up the nonrepeated values in a given field, based on the unique values defined by the sql_distinct_key parameter.

Consider a table like this:

Order Item ID Order ID Order Shipping
1 1 10.00
2 1 10.00
3 2 20.00
4 2 20.00
5 2 20.00

In this situation you can see that there are multiple rows for each order. Consequently, if you added a simple measure of type: sum for the order_shipping column, you would get a total of 80.00, even though the total shipping collected is actually 30.00.

# Will NOT calculate the correct shipping amount measure: total_shipping { type: sum sql: ${order_shipping} ;; }

To get an accurate result, you can explain to Looker how it should identify each unique entity (in this case, each unique order) by using the sql_distinct_key parameter. This will calculate the correct 30.00 amount:

# Will calculate the correct shipping amount measure: total_shipping { type: sum_distinct sql_distinct_key: ${order_id} ;; sql: ${order_shipping} ;; }

Please note that every unique value of sql_distinct_key must have just one corresponding value in sql. In other words, the above example works because every row with an order_id of 1 has the same order_shipping of 10.00, every row with an order_id of 2 has the same order_shipping of 20.00, and so on.

Top