End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Reports with revenue in categories
Summary

Instruction

Excellent! Now, we want to show the total revenue (as a reference point) next to the revenue of each category. We can use the following trick:

WITH AllCategories AS (
  SELECT SUM(Amount) AS TotalSum
  FROM Orders
)
SELECT 
  CategoryName,
  SUM(OI.Amount) AS CategoryRevenue,
  AC.TotalSum
FROM AllCategories AC, OrderItems OI
JOIN Orders O
  ON O.OrderId = OI.OrderId
JOIN Products P
  ON OI.ProductId = P.ProductId
JOIN Categories C
  ON P.CategoryId = C.CategoryId
GROUP BY CategoryName, AC.TotalSum;

The new construction you can see is called a Common Table Expression (CTE). It creates a temporary table named AllCategories that has a single column named TotalSum. In the outer query, we add this temporary table to the FROM clause. We don't need a JOIN because we're separating AllCategories from OrderItems with a comma. This indicates they are two different tables and they cannot be joined for a given column. Separating two tables with a comma means that each row from the first table will be joined with each row from the second table. In our case, AllCategories contains only a single row, so that row will be added to each row of OrderItems before any other joins are made.

We can then add the TotalSum column from AllCategories as the third column in the SELECT clause. We also add TotalSum in the GROUP BY clause.

Exercise

Create a report that shows the category revenue values for all orders placed in 2017. Show three columns: CategoryName, CategoryRevenue, and TotalSum. The last column should show the total revenue from 2017 for all rows.

Stuck? Here's a hint!

Add a WHERE clause in the CTE and in the outer query.