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 (
SUM(UnitPrice * Quantity) AS TotalPrice
FROM Orders O
JOIN OrderItems OI
ON O.OrderID = OI.OrderID
GROUP BY O.OrderID, O.CustomerID
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.