Each view file in your LookML project defines a single *view* within Looker, which specifies a table to query and which fields --- dimensions and measures --- from that table will be surfaced in the Looker UI. A [view](https://docs.cloud.google.com/looker/docs/2602/reference/param-view-view) corresponds to either a single table in your database or a single [derived table](https://docs.cloud.google.com/looker/docs/2602/derived-tables).

In this guide, you'll learn about the following topics:

- [How elements of a LookML view file relate to SQL](https://docs.cloud.google.com/looker/docs/2602/sql-experts-view#view_sql)
- How to create new dimensions by using [raw SQL](https://docs.cloud.google.com/looker/docs/2602/sql-experts-view#sql_case), [built-in LookML case logic](https://docs.cloud.google.com/looker/docs/2602/sql-experts-view#lookml_case), or [built-in LookML bin logic](https://docs.cloud.google.com/looker/docs/2602/sql-experts-view#lookml_tier_bin)

To learn more about using SQL to define and customize derived tables in LookML using SQL, see
[SQL concepts for derived tables](https://docs.cloud.google.com/looker/docs/2602/sql-experts-derived-tables).

## The view

Here is an example of a view file called `users.view`, which includes definitions for the database table that will be queried along with several dimensions and measures:

    view: users {
      sql_table_name: thelook.users ;;

      dimension: id {
        primary_key: yes
        type: number
        sql: ${TABLE}.id ;;
      }

      dimension: age {
        type: number
        sql: ${TABLE}.age ;;
      }
       measure: average_age {
        type: average
        sql: ${age} ;;  }

      dimension_group: created {
        type: time
        timeframes: [raw, time, date, week, month, quarter, year]
        sql: ${TABLE}.created_at ;;
      }

      measure: count {
        type: count
      }
    }

The first element of the view definition is the [`sql_table_name`](https://docs.cloud.google.com/looker/docs/2602/reference/param-view-sql-table-name) parameter, which specifies the table in your database that will be queried by a view. This value is the only place in the entire model where the table name is defined, because all other references to the view will use the table alias `${TABLE}`. If you want to change the database table name, it only needs to be changed in the `sql_table_name` parameter. There are some [things to consider](https://docs.cloud.google.com/looker/docs/2602/reference/param-view-sql-table-name#things_to_consider) when you reference a database table.

Looker uses the `sql_table_name` value to write its SQL FROM clause, followed by the view name, which becomes the table alias. The SQL equivalent would look like this:

    FROM `thelook`.`users` AS `users`

Looker uses the view's defined dimensions and measures to generate its SQL SELECT clause. Each dimension defines the [type](https://docs.cloud.google.com/looker/docs/2602/reference/param-dimension-filter-parameter-types#type_definitions) of dimension --- such as string, number, or Boolean --- and a [`sql`](https://docs.cloud.google.com/looker/docs/2602/reference/param-field-sql) LookML parameter that references the dimension within the view, using the table alias. For a dimension called `age`, see the following example:

      dimension: age {
        type: number
        sql: ${TABLE}.age ;;
      }

When Looker creates the SQL to send to your database, Looker substitutes the alias for the view into the `${TABLE}`. For the `age` dimension from the previous example, Looker would produce a SELECT clause like the following:

    SELECT `users`.`age` AS `users.age`

Measures are often aggregations that are performed on dimensions. You specify the dimension alias in a measure's `sql` expression. For example, a measure that calculates the average of the `age` dimension may have a `sql` expression with the alias `${age}` in it, as in the following example:

      dimension: age {
        type: number
        sql: ${TABLE}.age ;;
      }

      measure: average_age {
        type: average
        sql: ${age} ;;
      }

If you rename the `age` dimension, its new alias propagates to any of its dimension alias references.

## Customizing a view file

You can customize the SQL expressions of your view file or use Looker's built-in LookML logic to mimic the logic of a SQL expression.

### Using a SQL expression

Suppose that you want to divide the age data into four cohorts, with users younger than 18 defined as "Youth", users aged 18-35 as "Young Adult", users aged 36-65 as "Older Adult", and users 65 and older as "Senior". To perform this division, you must define a new dimension --- say, `dimension: age_cohort` --- with a `sql` expression that captures these cohorts. The following LookML dimension definition uses a CASE statement that is appropriate for a MySQL database connection:

    dimension: age_cohort {
      type: string
      sql:
        CASE
          WHEN ${age} < 18 THEN 'Youth'
          WHEN ${age} < 35 THEN 'Young Adult'
          WHEN ${age} < 65 THEN 'Older Adult'
          ELSE 'Senior'
        END ;;
    }

Now that you have defined your age cohort as a dimension, you can reuse the CASE logic by including the age cohort dimension in your Explore queries.

When you create an Explore query with the age cohort dimension, you can use the SQL tab of the Explore to [view the SQL that Looker generates](https://docs.cloud.google.com/looker/docs/2602/how-looker-generates-sql). With the age cohort dimension, the SQL will look something like this:

    SELECT
    CASE
      WHEN users.age < 18 THEN 'Youth'
      WHEN users.age < 35 THEN 'Young Adult'
      WHEN users.age < 65 THEN 'Older Adult'
      ELSE 'Senior'
    END  AS `users.age_cohort`,
    AVG(`age`) AS `users.average_age`,
    COUNT(*) AS `users.count`
    FROM
      `thelook`.`users` AS `users`
    GROUP BY
      1
    ORDER BY
      2 DESC
    LIMIT 500

### Using Looker's built-in case logic

You can achieve the same effect as a SQL CASE statement with an expression that is database-independent. The LookML [`case`](https://docs.cloud.google.com/looker/docs/2602/reference/param-field-case) parameter lets you define the cohort buckets that are made up of `when` statements that use `sql` expressions to capture specific conditions and strings for labeling the results.

The following is an example of the same new `age_cohort` dimension that is written with the `case` LookML parameter:

      dimension: age_cohort {
        case: {
          when: {
            sql: ${age} < 18 ;;
            label: "Youth"
          }
          when: {
            sql: ${age} < 35 ;;
            label: "Young Adult"
          }
          when: {
            sql: ${age} < 65 ;;
            label: "Middle-aged Adult"
          }
          else: "Older Adult"
        }
      }

At runtime, Looker builds the correct SQL CASE syntax for your database. In addition, Looker builds another expression to handle sorting the groups, so the resulting labels won't just be sorted alphanumerically (unless you [define the sort order](https://docs.cloud.google.com/looker/docs/2602/reference/param-field-case#choosing_the_sort_order_of_labels_with_alpha_sort) as alphanumeric). Looker builds a resulting SQL query that resembles the following:

    SELECT
    CASE
      WHEN users.age < 18  THEN '0'
      WHEN users.age < 35  THEN '1'
      WHEN users.age < 65  THEN '2'
      ELSE '3'
    END AS `users.age_cohort__sort_`,
    CASE
      WHEN users.age < 18  THEN 'Youth'
      WHEN users.age < 35  THEN 'Young Adult'
      WHEN users.age < 65  THEN 'Older Adult'
      ELSE 'Senior'
    END AS `users.age_cohort`,
    AVG(`age`) AS `users.average_age`,
    COUNT(*) AS `users.count`
    FROM
      `thelook`.`users` AS `users`
    GROUP BY
      1,
      2
    ORDER BY
      1
    LIMIT 500

### Using Looker's built-in bin or tier logic

Another method for specifying how numeric values should be grouped uses Looker's built-in [`bin`](https://docs.cloud.google.com/looker/docs/2602/reference/param-dimension-filter-parameter-types#bin) or [`tier`](https://docs.cloud.google.com/looker/docs/2602/reference/param-dimension-filter-parameter-types#tier) parameter types. The `type:bin` is used in conjunction with the `bins` parameter --- likewise, the `type: tier` is used in conjunction with the `tiers` parameter --- to separate a numeric dimension into a set of number ranges. The trade-off is that you cannot define labels for each bin.

The following LookML example uses the `bins` parameter in a dimension to define the minimum value in each set:

      dimension: age_cohort {
        type: bin
        bins: [18,36,65]
        style: integer
        sql: ${age} ;;
      }

You can use the `tiers` parameter in a dimension exactly the same way. For example:

      dimension: age_cohort {
        type: tier
        tiers: [18,36,65]
        style: integer
        sql: ${age} ;;
      }

Looker then generates something like the following SQL statement:

    SELECT
    CASE
      WHEN users.age  < 18 THEN '0'
      WHEN users.age  >= 18 AND users.age  < 36 THEN '1'
      WHEN users.age  >= 36 AND users.age  < 65 THEN '2'
      WHEN users.age  >= 65 THEN '3'
      ELSE '4'
    END AS `users.age_cohort__sort_`,
    CASE
      WHEN users.age  < 18 THEN 'Below 18'
      WHEN users.age  >= 18 AND users.age  < 36 THEN '18 to 35'
      WHEN users.age  >= 36 AND users.age  < 65 THEN '36 to 64'
      WHEN users.age  >= 65 THEN '65 or Above'
      ELSE 'Undefined'
    END AS `users.age_cohort`,
    AVG(`age`) AS `users.average_age`,
    COUNT(*) AS `users.count`
    FROM
      `thelook`.`users` AS `users`
    GROUP BY
      1,
      2
    ORDER BY
      1
    LIMIT 500

## Related resources

- [SQL concepts for joins](https://docs.cloud.google.com/looker/docs/2602/sql-experts-joins)
- [SQL concepts for derived tables](https://docs.cloud.google.com/looker/docs/2602/sql-experts-derived-tables)
- [Using Looker's SQL generator](https://www.googlecloudcommunity.com/gc/Technical-Tips-Tricks/Using-Looker-s-SQL-Generator/ta-p/814623)
- [How Looker generates SQL](https://docs.cloud.google.com/looker/docs/2602/how-looker-generates-sql)
- [Looker cookbook: Maximizing code reusability with DRY LookML](https://docs.cloud.google.com/looker/docs/2602/best-practices/dry-lookml-cookbook)