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


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:

  SUM(oi.amount) AS total_revenue 
FROM order_items oi
JOIN orders o
  ON o.order_id = oi.order_id
JOIN products p
  ON oi.product_id = p.product_id
JOIN categories c
  ON p.category_id = c.category_id
WHERE order_date >= '2016-01-01'
  AND order_date < '2017-01-01'
GROUP BY category_name;

Note that to filter the order_date column, we joined the order_items and orders tables.


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

Stuck? Here's a hint!

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