Error: Non-Unique value/primary key (or sql_distinct_key), value overflow or collision when computing sum

This page will help you troubleshoot this Looker error: 

  Non-unique value/primary key (or sql_distinct_key), value overflow or collision when computing sum

One of several issues can cause this error:

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 concatenate fields to create your own primary key dimension.

Using row_number to generate a primary key for a derived table

If you receive this error with a derived table, you can use the row_number() window function in Postgres and Redshift databases to make a unique field. This field can then be used as a primary key: 

  view: derived_table_name {
    derived_table {
      sql:
      SELECT
      row_number() OVER(ORDER BY created_at) AS prim_key,
      *
      FROM orders ;;
    }

    dimension: prim_key {
      type: number
      primary_key: yes
      sql: ${TABLE}.prim_key ;;
    }
  }

In MySQL, you can use a variable that iterates every row to achieve the same effect: 

  view: derived_table_name {
    derived_table {
     sql:
      SELECT
      CAST(@rownum := @rownum + 1 AS UNSIGNED) AS prim_key, t.*
      FROM orders t,
      (SELECT @rownum := 0) r ;;
    }

    dimension: prim_key {
      type: number
      primary_key: yes
      sql: ${TABLE}.prim_key ;;
    }
  }

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

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, and 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 measure that caused the problem.