How to count a non-primary key

Looker will not be updating this content, nor guarantees that everything is up-to-date. 



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.

Version history
Last update:
‎05-12-2022 02:14 PM
Updated by: