Instruction
Very well done! Now, our previous report showed the categories as columns and the time periods as rows. We can switch this, showing the time periods as columns and the categories as rows. The report will look like this:
| CategoryName | OverallCategoryRevenue | Revenue2016 | Revenue2017 | Revenue2018 |
|---|---|---|---|---|
| Confections | 167357.26 | 29685.56 | 82657.75 | 55013.95 |
| Meat/Poultry | 163022.37 | 28813.66 | 80975.12 | 53233.59 |
| Produce | 99984.57 | 13885.78 | 54940.76 | 31158.03 |
Take a look at the query:
SELECT CategoryName, SUM(OI.Amount) AS OverallCategoryRevenue, SUM(CASE WHEN OrderDate >= '20160101' AND OrderDate < '20170101' THEN OI.Amount ELSE 0.0 END) AS Revenue2016, SUM(CASE WHEN OrderDate >= '20170101' AND OrderDate < '20180101' THEN OI.Amount ELSE 0.0 END) AS Revenue2017, SUM(CASE WHEN OrderDate >= '20180101' AND OrderDate < '20190101' THEN OI.Amount ELSE 0.0 END) AS Revenue2018 FROM OrderItems OI JOIN Orders O ON OI.OrderId = O.OrderId JOIN Products P ON P.ProductId = OI.ProductId JOIN Categories C ON C.CategoryId = P.CategoryId GROUP BY C.CategoryId, CategoryName;
This time we don't group by time periods. Instead, we group by product categories. On the other hand, we have to define separate columns, using SUM(CASE WHEN...) for each period we want to compare.
This report format is useful when we have many categories to compare but few time periods.
Exercise
Show the category revenue for each quarter of 2017. Show the following columns: CategoryName, OverallCategoryRevenue, RevenueQ1, RevenueQ2, RevenueQ3, and RevenueQ4.
Stuck? Here's a hint!
You can check if a given order was placed in Q1 by using:
SUM(CASE
WHEN DATEPART(Quarter, OrderDate) = 1
THEN OI.Amount
ELSE 0.0
END) AS RevenueQ1
Remember about filtering the rows for orders from 2017.
