Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Reports with revenue in categories
Summary

Instruction

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:

SELECT 
  CategoryName,
  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.

Exercise

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'.