Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic multi-level aggregation
Multi-level aggregation in groups
Multi-level aggregation with custom classification
Three or more aggregation levels
14. Three aggregation levels – example 2
Summary

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 ...;