Question

Select Count Having Count from Another Table

  • 29 October 2021
  • 0 replies
  • 25 views

Returning a measure filtered on a measure from a joined explore could return unexpected results. For example if the primary explore is “Users” and a “Sales” explore is left joined to it and we try to return a count of users who have ten or more sales, Looker would generate the following SQL:

SELECT

    COUNT(*) AS users.count

FROM

    users

LEFT JOIN sales ON users.id = sales.user_id

HAVING (( COUNT( DISTINCT sales.id ) ) >= 10)


Without a group by, the having clause will have no effect on the count of users returned. Whereas if we selected users.id instead of a count, we’ll get the expected results. Is it possible Looker could generate this query differently? For example could Looker put
 

SELECT

    users.id

FROM

    users

LEFT JOIN sales ON users.id = sales.user_id

GROUP BY 1

HAVING (( COUNT( DISTINCT sales.id ) ) >= 10)

 

into a CTE and then select a count of users.id from that? Forgive me if I missed an explanation for this behavior in doc somewhere.


0 replies

Be the first to reply!

Reply