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, there are multiple rows for each order. If you added a simple measure of type: sum for the order_shipping column, you 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} ;;
}
Every unique value of sql_distinct_key must have just one corresponding value in sql. This 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, etc.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-14 UTC."],[],[],null,["# sql_distinct_key\n\n\u003cbr /\u003e\n\nUsage\n-----\n\n```\nview: view_name {\n measure: field_name {\n type: sum_distinct\n sql_distinct_key: ${my_field_name} ;;\n }\n}\n```\n\nDefinition\n----------\n\nThe `sql_distinct_key` parameter is used with measure types that perform aggregations on non-repeated values, specifically measures of type [`average_distinct`](/looker/docs/2512/reference/param-measure-types#average_distinct), [`median_distinct`](/looker/docs/2512/reference/param-measure-types#median_distinct), [`percentile_distinct`](/looker/docs/2512/reference/param-measure-types#percentile_distinct), and [`sum_distinct`](/looker/docs/2512/reference/param-measure-types#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](https://community.looker.com/technical-tips-tricks-1021/the-problem-of-sql-fanouts-30232).\n\nFor example, `type: sum_distinct` adds up the nonrepeated values in a given field, based on the unique values defined by the [`sql_distinct_key`](/looker/docs/2512/reference/param-field-sql-distinct-key) parameter.\n\nConsider a table like this:\n\nIn this situation, there are multiple rows for each order. If you added a simple measure of `type: sum` for the `order_shipping` column, you get a total of 80.00, even though the total shipping collected is actually 30.00. \n\n # Will NOT calculate the correct shipping amount\n measure: total_shipping {\n type: sum\n sql: ${order_shipping} ;;\n }\n\nTo 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: \n\n # Will calculate the correct shipping amount\n measure: total_shipping {\n type: sum_distinct\n sql_distinct_key: ${order_id} ;;\n sql: ${order_shipping} ;;\n }\n\nEvery unique value of `sql_distinct_key` must have just one corresponding value in [`sql`](/looker/docs/2512/reference/param-field-sql). This 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, etc."]]
