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 sql_distinct_key
• 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.

Quick Fix

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 sql_distinct_key

If any of the measures in your query are of the type sum_distinct, there may be a uniqueness mismatch between the sql_distinct_key and sql parameters of that measure.

Quick Fix

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

Quick Fix

To resolve this problem, add the primary key from view B to the problem measure with the sql_distinct_key parameter.

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:

1. Find the dimension within the measure that combines values from multiple joined views.
2. Find the views that are referenced by that dimension.
3. Concatenate the primary keys from those views using your SQL dialect's concatenation function.
4. Place that concatenated key into a sql_distinct_key parameter on the problem measure.