# SELECT Clause

The `SELECT` clause retrieves columns from a table.

`SELECT` query syntax takes the form of a required `SELECT` statement followed by one or more optional clauses, such as `WHERE`, `GROUP BY`, and `ORDER BY`.

For a simple column projection, SQL outputs the results with the name of the column provided. If you include additional expressions and functions, you must use an alias to name the column output. Include an alias by following the structure `{expression} {AliasName}` or `{expression as AliasName}`. The "as" is not required.

:::note
Unlike other versions of SQL, the CRM Analytics SQL server throws an error if you don't include an alias. It does not automatically provide an alias name.
:::

Let's look at an example.

```nolang
SELECT City, COUNT(*) as StoreCount
FROM "Superstore"
GROUP BY City;

```

Here are the first ten results.

| City        | StoreCount |
| ----------- | ---------- |
| Aberdeen    | 1          |
| Abilene     | 1          |
| Akron       | 21         |
| Albuquerque | 14         |
| Alexandria  | 16         |
| Allen       | 4          |
| Allentown   | 7          |
| Altoona     | 2          |
| Amarillo    | 10         |
| Anaheim     | 27         |

This query returns a table with two columns, `City` and the alias `StoreCount`. `StoreCount` is the column name given to the expression, `COUNT(*)`, which means count the number of rows for each city in the Superstore table. The `GROUP BY` clause groups all of the store counts by the `City` column.

`SELECT` supports the following operators.

| Type                 | Operator Name      | Supported Types          |
| -------------------- | ------------------ | ------------------------ |
| Arithmetic Operators | +                  | Numeric, String          |
|                      | -                  | Numeric                  |
|                      | *                  | Numeric                  |
|                      | /                  | Numeric                  |
|                      | %                  | Numeric                  |
| Comparison Operators | !=, =              | Numeric, String, Boolean |
|                      | <, <=, >, >=       | Numeric, String          |
|                      | `IN`, `NOT IN`     | Numeric, String          |
| Logical Operators    | `AND`, `OR`, `NOT` | Boolean                  |
| Case Operators       | `CASE ... END`     | Numeric, String, Boolean |

`SELECT` supports arithmetic operators between measure fields and using "+" between string fields.

- **[FROM Clause](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-from.md)**

  The `FROM` clause defines which table or table function to query. CRM Analytics SQL supports using `FROM` with a single table only. You can’t select from multiple tables.

- **[WHERE Clause](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-where.md)**

  By default, a SQL query retrieves every row in your dataset. Use the optional `WHERE` clause to restrict your query results to a conditional expression.

- **[Boolean Expressions in SELECT Clause](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-boolean.md)**

  A boolean expression is a logical statement that returns either `true` or `false`.

- **[GROUP BY Clause](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-groupby.md)**

  The `GROUP BY` clause organizes the rows returned from a `SELECT` statement into groups. Within each group, you can apply an aggregate function, like `count()` or `sum()` to get the number of items or sum, respectively.

- **[GROUP BY ROLLUP](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-groupby-rollup.md)**

  `ROLLUP` is a sub-clause of `GROUP BY` that creates and displays aggregations of column data. The results output of `ROLLUP` is based on column order in your query.

- **[GROUPING()](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-grouping.md)**

  Use the `GROUPING()` clause with `ROLLUP` to determine whether null values are the result of a `ROLLUP` operation or part of your dataset. `GROUPING()` returns 1 if the null value is a subtotal generated by a rollup. It returns 0 otherwise.

- **[HAVING Clause](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-having.md)**

  Use the `HAVING` clause to filter grouped results from grouped columns, aggregate functions, or grouping functions.

- **[ORDER BY Clause](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-orderby.md)**

  `SELECT` returns rows in an unspecified order by default. To sort returned rows in ascending or descending order, use the `ORDER BY` clause.

- **[LIKE Clause](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-like.md)**

  Use `LIKE` to match single characters and patterns found anywhere in a string—beginning, ending, or somewhere in between.

- **[BETWEEN Operator](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-between.md)**

  Use `BETWEEN` to check whether values fall within a given range. `BETWEEN` accepts numeric, string, and date data types, and can be used with aggregate, window, and math functions.

- **[LIMIT Clause](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-limit.md)**

  The `LIMIT` clause specifies the maximum number of rows to return. If no `LIMIT` clause is specified, then SQL returns all rows.

- **[FETCH Clause](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-fetch.md)**

  The `FETCH` clause specifies the number of rows to return. If no `FETCH` clause is specified, then SQL returns all rows.

- **[OFFSET Clause](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-offset.md)**

  By default, a SQL query retrieves every row in your dataset. Use the optional `WHERE` clause to restrict your query results to a conditional expression.

- **[CASE Statements](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-case.md)**

  Use case statements to express if/then logic. A case statement always has a pair of `WHERE` and `THEN` statements. CRM Analytics supports the simple and searched forms of case expressions in a `SELECT` statement. Case statements have two formats: simple and searched.

- **[COALESCE()](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-coalesce.md)**

  You can use the `coalesce()`function as shorthand for case statements. The `coalesce()` function replaces null values in your dataset with another value. The function takes a series of arguments and returns the first value that is not null.

- **[NULLIF()](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-nullif.md)**

  Use the `nullif()` function as shorthand for a searched case statement where two equal expressions return null.

- **[UNION Operator](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-union.md)**

  Use the `UNION` operator to combine the results of two or more `SELECT` statements. The joined statements must have the same number of columns and the same data types for corresponding columns.

- **[Subquery](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-subquery.md)**

  A subquery is a query that is nested inside a `SELECT` statement. Nesting queries allows you to perform multi-step operations.

- **[Window Functions](/docs/analytics/bi-dev-guide-sql/guide/bi-sql-window.md)**

  A window function lets you perform calculations on a selection—or "window"—of rows that are related to the current row. Unlike a regular aggregate function, such as `avg()`, `sum()`, or `count()`, the row output of a window function isn’t grouped into a single row.