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
15. Summary

## Instruction

Good job! Before we move on to the final quiz for this course, let's review what we've learned:

1. To create a report with history-to-date category revenue values:
SELECT
CategoryName,
SUM(Amount) AS TotalRevenue
FROM OrderItems OI
JOIN Products P
ON OI.ProductId = P.ProductId
JOIN Categories C
ON P.CategoryId = C.CategoryId
GROUP BY CategoryName;

2. To show category revenue values and their relation to the total revenue, use:
WITH AllCategories AS (
SELECT SUM(Amount) AS TotalSum
FROM Orders
)
SELECT
CategoryName,
SUM(OI.Amount) AS CategoryRevenue,
SUM(OI.Amount) / CAST(AC.TotalSum AS DECIMAL(10,2)) AS TotalRevenueRatio
FROM AllCategories AC, 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
GROUP BY CategoryName,
AC.TotalSum;

3. To show the category revenue for selected categories for each time period or category revenue for each category and selected time periods, use SUM(CASE WHEN) along with GROUP BY. For instance:
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);


How about a short quiz to finish?

## Exercise

Click to continue.