Knowledge Drop

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

  • 5 April 2021
  • 1 reply
  • 216 views

Userlevel 5
Badge
  • New Member
  • 163 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: ${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.

Reply