Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Revenue Trend Analysis in SQL Server

Introduction
Reports with revenue in categories
Summary

Instruction

Nice! We can also use additional filters when we calculate the total revenue in each category. For instance, if we wanted to show the total revenue in each category for orders placed in 2016, we could use the following query:

SELECT 
  CategoryName,
  SUM(OI.Amount) AS TotalRevenue 
FROM 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
WHERE OrderDate >= '20160101' AND OrderDate < '20170101'
GROUP BY CategoryName;

Note that to filter the OrderDate column, we joined the OrderItems and Orders tables.

Exercise

Generate a history-to-date category revenue report based on orders shipped to the United States. The names of the columns should be CategoryName and TotalRevenue.

Stuck? Here's a hint!

Use the ShipCountry column. It should have the value 'USA'.