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:

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.