CTE Example
Let us now better understand the concept of CTEs by taking a look at how to create one. First of all, we should have a table that we’ll be working with; in our example, that would be the following table (named Table1):
%20in%20SQL/table%201.png)
TEXT/X-SQL
1WITH Avg_age AS (
2 SELECT
3 EXTRACT(year FROM DateOfOrder) AS year,
4 NumberOfItems,
5 AVG(Age) AS AverageAge
6 FROM Table1
7 GROUP BY EXTRACT(year FROM DateOfOrder) , NumberOfItems
8)
9SELECT
10 Table1.*,
11 avg.Avg_age
12FROM Table1
13JOIN Avg_age avg
14 ON EXTRACT(year FROM Table1.DateOfOrder) = avg.year
15 AND Table1.NumberOfItems = avg.NumberOfItemsThe resulting table, after the execution of this query, would look like this:
%20in%20SQL/result_table.png)
