Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic CTEs
7. Syntax with columns
Summary

## Instruction

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

WITH SomeName (CteColumns) AS (
YourCte
)

SELECT
...
FROM SomeName


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

WITH ProjectRevenue (Id, SumAmount) AS (
SELECT
Project.Id,
SUM(Amount)
FROM Project
INNER JOIN Donation
ON Donation.ProjectId = Project.Id
GROUP BY Project.Id
)

SELECT
Id,
SumAmount
FROM ProjectRevenue;


Defining the columns this way is not required. Why would we use it? For one thing, it increases the readability of your query. For another, while simple columns inside CTEs don't require aliases, those containing aggregates and other function results do. Columns like SUM(Amount) or COUNT(ProjectId) must be given names so that you can refer to them outside the CTE. One way to create aliases 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 for yourself: try to run the template query without column definitions or aliases and see what happens. Afterwards, look at the query and think how the columns should be named. Then, correct it and run the code again.