SQL WITH (CTE)
Name the steps of a query (common table expressions).
Syntax
WITH name AS (SELECT …), other AS (SELECT … FROM name) SELECT … FROM other;
Each CTE can use the ones before it. CTEs make long queries readable and testable.
Example
Query (on the shop database used in the SQL course)
WITH order_totals AS ( SELECT order_id, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id ) SELECT COUNT(*) AS orders, ROUND(AVG(total), 2) AS avg_total, MAX(total) AS biggest FROM order_totals;
Result
| orders | avg_total | biggest |
|---|---|---|
| 51 | 459.92 | 1711.0 |
1 row
Related
Learn it step by step in the free lesson CTEs & Window Functions, with exercises that check your queries.