sql

LookML
Version

On this Page
Docs Menu

Go to Field Parameter List

This section refers to the sql parameter that is part of a dimension, measure, filter, or dimension_group

sql can also be used as part of a derived table, as described here

Definition

The sql parameter takes several types of SQL expressions that will define a dimension, measure, or filter. The expression you need to write varies based on the type of field you are creating. More details about dimension and filter types can be found here, while more details about measures types can be found here.

sql for Dimensions

The sql block for dimensions can generally take any valid SQL that would go into a single column of a SELECT statement. These statements generally rely on Looker’s substitution operator, which takes one of the following 3 forms:

  • ${TABLE}.column_name references a column in the table that is connected to the view you’re working on
  • ${dimension_name} references a dimension within the view you’re working on
  • ${view_name.dimension_name} references a dimension from another view

Note that measures shouldn’t be referenced in the sql of a dimension.

A very simple dimension that takes the value directly from a column called revenue might look like:

- dimension: revenue_in_cents sql: ${TABLE}.revenue type: number

A dimension that relies on another dimension in the same view might look like this:

- dimension: revenue_in_dollars sql: ${revenue_in_cents} / 100 type: number

A dimension that relies on another dimension in a different view might look like this:

- dimension: profit_in_dollars sql: ${revenue_in_dollars} - ${inventory_item.cost_in_dollars} type: number

More advanced SQL users can perform relatively advanced calculations, including correlated sub-queries (note: not all database dialect support correlated subqueries):

- dimension: user_order_sequence_number type: number sql: | ( SELECT COUNT(*) FROM orders AS o WHERE o.id <= ${TABLE}.id AND o.user_id = ${TABLE}.user_id )

For further details, refer to the documentation for a specific dimension type.

sql for Dimension Groups

The sql parameter for a dimension_group takes any valid SQL expression that contains data in a timestamp, datetime, date, epoch, or yyyymmdd format.

sql for Measures

The sql block for measures typically takes one of two forms:

  • The SQL over which an aggregate function (such as COUNT, SUM, AVG) will be performed, again using Looker’s substitution operator as described above
  • A value based on several other measures

For example, if we wanted to calculate the total revenue in dollars, we might use:

- measure: total_revenue_in_dollars sql: ${revenue_in_dollars} type: sum

Or if we wanted to calculate our total profit, we might use:

- measure: total_revenue_in_dollars sql: ${total_revenue_in_dollars} - ${inventory_item.total_cost_in_dollars} type: number

For further details, refer to the documentation for a specific measure type.

SQL Math Challenges

There are two frequent challenges that come up with division in the sql parameter.

First, if you are using division in your calculation, you want to protect against the possibility of dividing by zero, which will cause a SQL error. To do so, use the SQL NULLIF function. For example, this example means “if the denominator is zero, treat it like NULL instead”:

- measure: active_users_percent sql: ${active_users} / NULLIF(${users}, 0) type: number

Another issue is the way that SQL handles integer math. If you divide 5 by 2, most people expect the result to be 2.5. However, many SQL dialects will return the result as just 2, because when it divides two integers it also gives the result as an integer. To address this, you can multiply the numerator by a decimal number to force SQL into returning a decimal result. For example:

- measure: active_users_percent sql: 100.00 * ${active_users} / NULLIF(${users}, 0) type: number

Liquid Variables with sql

You can also use Liquid variables with the sql parameter. Liquid variables let you access data such as the values in a field, data about the field, and filters applied to the field.

For example, this dimension masks a customer password according to a Looker user attribute:

- dimension: customer_password sql: {% if _user_attributes['pw_access'] == 'yes' %} ${password} {% else %} "Password Hidden" {% endif %}

Still have questions?
Go to Discourse - or - Email Support
Top