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:

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

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

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

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:

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

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”:

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:

### 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: