Good job! The queries we've written so far all returned a single value. Let's change that.
Suppose we want to find the average order value for each customer from Canada. How can we do that? Let's find out.
WITH OrderTotalPrices AS (
SELECT
O.OrderID,
O.CustomerID,
SUM(UnitPrice * Quantity) AS TotalPrice
FROM Orders O
JOIN OrderItems OI
ON O.OrderID = OI.OrderID
GROUP BY O.OrderID, O.CustomerID
)
SELECT
C.CustomerID,
C.CompanyName,
AVG(TotalPrice) AS AvgTotalPrice
FROM OrderTotalPrices OTP
JOIN Customers C
ON OTP.CustomerID = C.CustomerID
WHERE C.Country = N'Canada'
GROUP BY C.CustomerID, C.CompanyName;
In the query above, we first write a CTE that calculates the total price (before discount) for each order. Note that we also put the CustomerID column in the SELECT clause so that we can refer to it in the outer query.
In the outer query, we use the AVG() function as before, but this time, we also group all rows by CustomerID. We also join the CTE with another table (Customers) so that we can show company names and select only those customers who come from Canada.
For each employee from the Washington (WA) region, show the average value for all orders they placed. Show the following columns: EmployeeID, FirstName, LastName, and AvgTotalPrice (calculated as the average total order price, before discount).
In the inner query, calculate the value of each order and select it alongside the ID of the employee who processed it. In the outer query, join the CTE with the Employees table to show all the required information and filter the employees by region.
You can use a template:
WITH OrderTotalPrices AS ( ... ) SELECT ... FROM OrderTotalPrices OTP JOIN ...
In the inner query, join Orders and OrderItems tables. In the outer query, join OrderTotalPrices and Employees tables.
