It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic CTE
7. Syntax with columns
Summary

Instruction

Great! There is an alternative CTE syntax, where we define the columns explicitly:

WITH some_name (cte_columns) AS ( your_cte )
  SELECT ... 
  FROM some_name

In other words, we now have two pairs of parentheses. First, we provide the names of the columns that our CTE will have. Second, we define the actual CTE, based on the columns we provided.

WITH project_revenue (id, sum_amount) AS (
  SELECT 
    project.id, 
    SUM(amount)
  FROM project
  JOIN donation 
    ON donation.project_id = project.id
  GROUP BY project.id
)

SELECT 
  id, 
  sum_amount 
FROM project_revenue;

The column definition is not required. Why would we use it? It increases the readability of your query. Also, while simple columns inside CTEs don't require aliases, aggregates and other function results do. Columns like SUM(amount) or COUNT(project_id) must be given names so that you can refer to them outside the CTE. One way to do this is to use the keyword AS, just as we did previously. Another way is to provide a list of columns. Either method is fine, but if you use neither, an error will occur.

Exercise

Check it out for yourself: try to run the template query without the column definition or aliases and see what happens. Then, correct it and run the code again.