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


Good! Let's get started. There are two queries which we can use for total revenue as follows:

SELECT SUM(Amount) AS TotalRevenue FROM Orders;


SELECT SUM(Amount) AS TotalRevenue FROM OrderItems;

Now, let's say we want to show the history-to-date revenue for various categories:

CategoryName TotalRevenue
Seafood 131261.75
Meat/Poultry 163022.37
Condiments 106047.11

Such a report can help us discover which product categories are more profitable. In this case, we can only use the second query (the one with the OrderItems table). Take a look:

  SUM(Amount) AS TotalRevenue 
FROM OrderItems OI
JOIN Products P
  ON OI.ProductId = P.ProductId
JOIN Categories C
  ON P.CategoryId = C.CategoryId
GROUP BY CategoryName;

This query is a bit more complicated because we had to join OrderItems with Products and Categories to get the category names used in the report.


Run the template query and see how it generates a row with the history-to-date revenue for each category.

Stuck? Here's a hint!