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.

Let’s look at an example.

Here are the first ten results.

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.

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

• FROM Clause

  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

  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

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

• GROUP BY Clause

  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

  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()

  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

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

• ORDER BY Clause

  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

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

• BETWEEN Operator

  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

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

• FETCH Clause

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

• OFFSET Clause

  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

  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()

  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()

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

• UNION Operator

  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

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

• Window Functions

  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.