Why is my measure of type:count being made into a COUNT DISTINCT in the SQL?

[sam8]

Knowledge Drop

Last tested: May 2020

A count measure usually creates a COUNT(*) in the SQL. However, if joins are involved, then a COUNT(*) would cause a fanout.

So to avoid this, Looker generates a COUNT DISTINCT on the view's primary key instead. This should give you the same results as a COUNT(*) on that table with no joins.

This content is subject to limited support.                

[cjones]

Hi Sam,

I found that explores that are joined on a one to one relationship directly to the primary explore return a count(*) instead of a count(distinct). For example if I have the following explore:
 

explore: users {

  join: phones {
    type: left_outer
    sql_on: ${users.id} = ${phones.user_id}
    relationship: one_to_one
  }
}

 Then from the Users explore if I select a count of phones I would get the following:
 

select
  count(*) AS "phones.count"
from 
  users as "users"

So if I had users that did not have a record in phones I would get unexpected results.