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

Perfect! Next, we'd like to compare revenue values in selected categories year-to-year, quarter-to-quarter, etc. This way, we can analyze whether the category revenue increases or decreases over time.

The report should look like this:

RevenueYear TotalRevenue BeveragesRevenue SeafoodRevenue
2016 208083.98 47919.00 19391.23
2017 617085.27 103924.32 66959.22
2018 440623.93 116024.88 44911.30

We could use the following query:

SELECT
DATEPART(Year, OrderDate) AS RevenueYear,
SUM(OI.Amount) AS TotalRevenue,
SUM(CASE
WHEN C.CategoryName = 'Beverages'
THEN OI.Amount
ELSE 0.0
END) AS BeveragesRevenue,
SUM(CASE
WHEN C.CategoryName = 'Seafood'
THEN OI.Amount
ELSE 0.0
END) AS SeafoodRevenue
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 DATEPART(Year, OrderDate)
ORDER BY DATEPART(Year, OrderDate);


As you can see, the second column contains the total revenue, which is a simple SUM() of all the values in the Amount column. However, we also added two columns which use the SUM(CASE WHEN...) construction. These will select only the Amount values for specific categories.

This type of report is typically used when there are few categories to compare over many reporting periods.

## Exercise

For each year and quarter, calculate the revenue for the 'Condiments' and 'Confections' categories. Show the following columns: RevenueYear, RevenueQuarter, TotalRevenue, CondimentsRevenue, and ConfectionsRevenue. Order the results by year and quarter.

### Stuck? Here's a hint!

You will need to use DATEPART() twice in each of the SELECT, the GROUP BY and the ORDER BY clauses:

DATEPART(Year, OrderDate), DATEPART(Quarter, OrderDate)