Knowledge Drop

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

  • 5 April 2021
  • 1 reply

Userlevel 5
  • Looker Staff
  • 172 replies

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.                

1 reply

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: ${} = ${phones.user_id}
relationship: one_to_one

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

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

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