Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Nested CTEs
10. Exercise 3
Summary

## Instruction

Excellent job! Another situation where nested CTEs can be useful is when comparing two groups of items. Take a look:

WITH Grouping AS (
SELECT
Id AS CityId,
CASE WHEN Region = N'Europe' THEN Region ELSE N'Other' END AS GroupName
FROM City
),
TotalSalesmanEarnings AS (
SELECT
SalesmanId,
GroupName,
SUM(AmountEarned) AS TotalAmount
FROM DailySales DS
INNER JOIN Salesman S
ON DS.SalesmanId = S.Id
INNER JOIN Grouping G
ON G.CityId = S.CityId
GROUP BY SalesmanId,
GroupName
)

SELECT
GroupName,
AVG(TotalAmount) AS AvgTotalAmount
FROM TotalSalesmanEarnings S
GROUP BY GroupName


We want to compare average earnings of sales reps from Europe with reps from other parts of the world. In the first CTE, we define the groups: cities within Europe are labeled N'Europe' and cities outside Europe are labeled N'Other'. In the second CTE, we compute the total amount earned for each salesperson and combine this information with the group definition for that person. In the outer query, we compute the group-level average.

## Exercise

Compare the average number of items sold by sales reps from the USA (Country = N'USA') with reps from other countries in the world.

Name the group column GroupName. In your query, use the values N'USA' and N'Other' to label the groups. The other column should be called Avg.

### Stuck? Here's a hint!

Modify the example query.