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


Nice! We can also use additional filters when we calculate the total revenue in each category. For instance, if we wanted to show the total revenue in each category for orders placed in 2016, we could use the following query:

  SUM(OI.Amount) AS TotalRevenue 
FROM 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
WHERE OrderDate >= '20160101' AND OrderDate < '20170101'
GROUP BY CategoryName;

Note that to filter the OrderDate column, we joined the OrderItems and Orders tables.


Generate a history-to-date category revenue report based on orders shipped to the United States. The names of the columns should be CategoryName and TotalRevenue.

Stuck? Here's a hint!

Use the ShipCountry column. It should have the value 'USA'.