Knowledge Drop

How to write a correlated subquery in a measure

  • 8 July 2021
  • 0 replies
  • 432 views

Userlevel 4

Last tested: Dec 9, 2019
 

The Problem

I want to write a correlated subquery, but I'd like to do it without using derived tables or table calculations.

A Solution

 

We can take advantage of the fact that type: number measures allow us to write basically any SQL in order to write a subquery. (Warning: Not all databases support correlated subqueries. These examples are written for MySQL.)

  1. Make a measure of type number so that Looker doesn't add any additional SQL.
  2. For the sql parameter, write a subquery enclosed in parentheses. In this case, we want a category count, so it would look something like:
    sql: (SELECT count(category) FROM table) ;;
  3. The tricky part here is that we want the count to listen to the email field in the table. So we need to write a correlated subquery to reference the outer email field. In this case it would look something like:
    sql: (SELECT count(category) FROM table WHERE users.email = `users.email`) ;;
    (When generating SQL for MySQL, Looker creates the field aliases in the general form `viewname.fieldname`.)
  4. At this point, the field will break if it's selected without the email. The user would see an error saying "unknown field `users.email`". To avoid this error message, let's use the _is_selected liquid parameter to capture that potential case and show a bogus value like -1.
    sql: {% if users.email._is_selected %}

    (SELECT count(category) FROM table WHERE users.email = `users.email`)

    {% else %} -1 {% endif %} ;;
  5. We can use the HTML parameter to show a nicer message whenever -1 is shown. (We could not have written this string in the SQL during step 4 because returning a string in a type: number measure returns null.)
    html: {% if value == -1 %}

    Please select users.email in order to use this field.

    {% else %} {{value}} {% endif %} ;;

That's it! Here is the full measure definition that we have been building up to:

measure: count_categories {

label: "Count of categories for this email address"

type: number

sql:

{% if users.email._is_selected %}

(SELECT count(category) FROM table WHERE users.email = `users.email`)

{% else %}

-1

{% endif %}

;;

html:

{% if value == -1 %}

Please select users.email in order to use this field.

{% else %}

{{value}}

{% endif %}

;;

}

 

This content is subject to limited support.                

 

 


0 replies

Be the first to reply!

Reply