Earn income with your data and sql skills

Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.

SQL WITH CTE

SQL NullIf

SQL Pivot

In SQL Server, WITH CTE defines a temporary named result set.

This result set is called a common table expression (CTE).

CTEs can make queries more readable and more efficient in execution.

Example

#

List annual sales with the best years first.

WITH Cte (Year, Count, Amount) AS (
  SELECT YEAR(OrderDate) AS Year,
         COUNT(Id) AS Count,
         SUM(TotalAmount) AS Amount
    FROM [Order]
   GROUP BY YEAR(OrderDate)
)

SELECT Year, Amount 
  FROM Cte
 ORDER BY Amount DESC

Result: 3 records

Year	TotalSales
2013	658388.75
2014	469771.34
2012	226298.50

Using WITH CTE

#

WITH CTE defines a temporary named result called a CTE.

A CTE is a query that can be used with SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

Instead of subqueries, CTEs are used to make the query more readable and efficient in execution.

A query can use multiple CTEs (see below).

Syntax

#

Syntax for the WITH CTE function.

WITH name (column1, column2, ..., columnN) AS (
  SELECT statement
)

name -- the name of the CTE which is referred in a subsequent query.

column1, ... -- column names representing the columns in the SELECT statement.

SELECT statement -- a query used to populate the CTE result set.

More Examples

#

WITH CTE with COLUMN

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

Problem: List the smallest and largest orders by month in 2013.

WITH Cte (Month, Min, Max) AS (
  SELECT MONTH(OrderDate) AS Month,
         MIN(TotalAmount) AS Min,
         MAX(TotalAmount) AS Max
    FROM [Order]
   WHERE YEAR(OrderDate) = 2013
   GROUP BY MONTH(OrderDate)
)

SELECT Month, 
       Min AS MinOrder,
       Max AS MaxOrder
  FROM Cte
 ORDER BY Month

Result: 12 records

Month	MinOrder	MaxOrder
1	49.80	11493.20
2	174.90	5793.10
3	147.00	10495.60
4	136.80	10588.50
5	110.00	10191.70

MULTIPLE NAMED CTEs with SELECT JOIN

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

Problem: List customers in the US and their order counts and order amounts for 2013.

WITH CteOrder (CustomerId, OrderCount, OrderAmount) AS (
  SELECT CustomerId,
         COUNT(Id),
         SUM(TotalAmount)
    FROM [Order]
   WHERE YEAR(OrderDate) = 2013
   GROUP BY CustomerId
),
CteCustomer (Id, FirstName, LastName) AS (
  SELECT Id, FirstName, LastName
    FROM Customer
   WHERE Country = 'USA'
)

SELECT FirstName, LastName, OrderCount, OrderAmount
  FROM CteOrder O
  JOIN CteCustomer C ON O.CustomerId = C.Id
 ORDER BY LastName

Result: 13 records

FirstName	LastName	OrderCount	OrderAmount
Art	Braunschweiger	2	2972.50
Karl	Jablonski	8	10262.55
Yoshi	Latimer	3	2283.20
Helvetius	Nagy	2	1333.30
Liz	Nixon	2	2955.40

#

Our Sql GROUP BY Tutorial
Our Sql Aggregrates Functions Reference
Our Sql Functions Reference