Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Reports with revenue in categories


Well done! It's time for an additional exercise.


Create a report of the category revenue for orders shipped to Germany. Show three columns: CategoryName, CategoryRevenue, and TotalRevenueRatio. The last column should show the ratio of category revenue to total revenue generated by orders shipped to Germany.

Stuck? Here's a hint!

Create a temporary table using:

WITH AllCategories AS (
  SELECT SUM(Amount) AS TotalSum
  FROM Orders
  WHERE ShipCountry = 'Germany'

Calculate the last column as:

SUM(OI.Amount) / CAST(AC.TotalSum AS decimal(10,2)) AS TotalRevenueRatio