How to count a non-primary key

Looker Tips & Resources Technical Tips & Tricks
1

If you would like to count (not count_distinct) a field that is not the primary key, you can do this by creating a measure of type: number and performing the count in the sql parameter:

measure: count_states {
 type: number
 sql: COUNT(${state}) ;;
}

This measure will generate the following SQL:

COUNT(users.state)

Measures of type: count_distinct can make use of a filters field parameter, while measures of type: number cannot. In addition, while this does not apply to COUNT DISTINCT, other aggregation types — like SUM, AVERAGE, and COUNT — should be represented with specific measure types rather than number.

These difference allow Looker to properly apply symmetric aggregates in order to avoid fanout values when exploring and using aggregations.

1 Like