Instruction
Good job! Now, let's take a look at a slightly different example.
Say we want to count the number of orders processed by each employee, and we only want to show the employees whose OrderCount is greater than the average OrderCount for all employees. Have a look:
WITH OrderCountEmployees AS (
SELECT
EmployeeID,
COUNT(OrderID) AS OrderCount
FROM Orders
GROUP BY EmployeeID
),
AvgOrderCount AS (
SELECT
AVG(OrderCount) AS AvgOrderCount
FROM OrderCountEmployees
)
SELECT
EmployeeID,
OrderCount,
AvgOrderCount
FROM OrderCountEmployees,
AvgOrderCount
WHERE OrderCount > AvgOrderCount;
In the first CTE, we count the number of orders processed by each employee. In the second CTE, we find the average order count based on the first CTE. But look what happens in the outer query: We simply provide both CTE names in the FROM clause, separated with a comma. Thanks to this, we can refer to columns from both CTEs in the WHERE clause and show all columns in the SELECT clause. As a result, each employee with an above-average order count will be shown with their order count. We'll also show the average count for reference:
| EmployeeID | OrderCount | AvgOrderCount |
|---|---|---|
| 1 | 123 | 92 |
| 2 | 96 | 92 |
| 3 | 127 | 92 |
| 4 | 156 | 92 |
| 8 | 104 | 92 |
Exercise
Among orders shipped to Italy, show all orders that had an above-average total value (before discount). Show the OrderID, OrderValue, and AvgOrderValue column. The AvgOrderValue column should show the same average order value for all rows.
Stuck? Here's a hint!
Use the following template:
WITH OrderValues AS ( ... ), AvgOrderValue AS ( ... ) SELECT ... FROM OrderValues, AvgOrderValue WHERE ...;
