This article can help you troubleshoot the "Non-unique value/primary key (or sql_distinct_key), value overflow or collision when computing sum" error in Looker.
This error can be caused by several issues:
- Most Commonly, a non-unique primary key
- Incorrect usage of
- Referencing a field across views when fanouts are involved
Non-Unique Primary Key
The most common cause of this error is that your query involves a non-unique primary key. You specify a primary key by using
primary_key: yes on a dimension, and it must be a dimension without any repeated values.
Once you identify the primary key dimensions in your query, you can test them for uniqueness in Looker's SQL Runner with this query:
SELECT COUNT(*), COUNT(DISTINCT your_primary_key) FROM your_table_name
If the counts in this query match, the primary key is unique. If the counts do not match, the primary key is not unique and appears in multiple rows. You will need to choose or create a new dimension as your primary key. If no single dimension contains entirely unique values, you may need to create your own primary key dimension.
Incorrect Usage of
If any of the measures in your query are of the type
sum_distinct, there may be a uniqueness mismatch between the
sql parameters of that measure.
Check out our
sum_distinct documentation page for these parameters' requirements.
Referencing Fields Across Views with Fanouts
Your query may use a measure from view A, but the measure references a field from view B. In this situation Looker will use the primary key from view A to calculate that measure. If your query involves a fanout, that may not be the correct primary key to use. (To become familiar with fanouts, check out our related blog entry.)
To resolve this problem, add the primary key from view B to the problem measure with the
What If None of the Listed Causes Apply, but the Error Still Occurs?
There is a very specific situation in which your primary key can be unique, and the other causes of this problem don't apply to your query, but this error still occurs. First, your query will involve multiple joins of
relationship: one_to_many. Second, one of the measures in your query will reference a dimension that combines values from multiple joined views.
To fix this problem, make a note of that measure, then follow these steps:
- Find the dimension within the measure that combines values from multiple joined views.
- Find the views that are referenced by that dimension.
- Concatenate the primary keys from those views using your SQL dialect's concatenation function.
- Place that concatenated key into a
sql_distinct_keyparameter on the problem measure.