Looker has two types of count measures: count and count_distinct. The count type of each of these has a few key differences, as described here.

type: count

Measures of type: count perform a COUNT of the primary key of the view, where this measure is defined. A type: count measure only counts the primary key of the view; it does not allow a sql parameter in this type of measure.

A measure of type: count is equivalent to a COUNT(id).

view: products {

  measure: number_of_products {
   type: count
   drill_fields: [product_details*] # optional
  }
}

type: count_distinct

Measures of type: count_distinct do take a sql parameter. Whenever you want to count a field that is not the primary key, use a count_distinct.

Example:

A measure defined like this:

measure: count_distinct_states {
 type: count_distinct
 sql: ${state} ;;
}

will produce this generated SQL:

COUNT(DISTINCT users.state)

Counting a Non-Primary Key

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.