Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic multi-level aggregation
3. Multi-level aggregation – explanation
Multi-level aggregation in groups
Multi-level aggregation with custom classification
Three or more aggregation levels
Summary

Instruction

The code in the previous exercise used an SQL concept known as a CTE, or a common table expression. Think of it as a temporary set of rows that you define and use later in the same query. CTEs are similar to subqueries.

The most basic syntax of any common table expression looks like this:

WITH some_name AS (
  -- your CTE
)
SELECT
  ... 
FROM some_name

You need to give your CTE a name (we used some_name in the example) and define it within a pair of parentheses. Then, once you close the bracket, you can select columns from the CTE as if it were a table. We will refer to the CTE as the "inner query" and the part after it as the "outer query." Note that you need to define your CTE first – i.e., before the outer query's SELECT.

Back to our example:

WITH OrderTotalPrices AS (
  SELECT
    O.OrderID, 
    SUM(UnitPrice * Quantity) AS TotalPrice
  FROM Orders O
  JOIN OrderItems OI
    ON O.OrderID = OI.OrderID
  GROUP BY O.OrderID
)

SELECT
  AVG(TotalPrice) AS AvgTotalPrice
FROM OrderTotalPrices;

Here, the CTE is called OrderTotalPrices and allows us to access two columns: OrderID and TotalPrice. In the outer query, we aggregate the TotalPrice column using AVG(TotalPrice). As a result, we'll get a single number in the report.

Exercise

The template code contains the query from the previous exercise. Your task is to modify it to show the average total price after discount. Rename the AvgTotalPrice column to AvgTotalDiscountedPrice.

Stuck? Here's a hint!

Change the inner query. Calculate the total price for an order as:

SUM(UnitPrice * Quantity * (1 - Discount))