Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Reports with revenue in 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 Next exercise to continue.