Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Reports with revenue in categories
Revenue in time periods for selected categories
Summary

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