Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Reports with revenue in categories
Summary

Instruction

Good! Let's get started. There are two queries which we can use for total revenue as follows:

SELECT SUM(amount) AS total_revenue FROM orders;

Or:

SELECT SUM(amount) AS total_revenue FROM order_items;

Now, let's say we want to show the history-to-date revenue for various categories:

category_name total_revenue
Seafood 131261.75
Meat/Poultry 163022.37
Condiments 106047.11

Such a report can help us discover which product categories are more profitable. In this case, we can only use the second query (the one with the order_items table). Take a look:

SELECT 
  category_name,
  SUM(amount) AS total_revenue 
FROM order_items oi
JOIN products p
  ON oi.product_id = p.product_id
JOIN categories c
  ON p.category_id = c.category_id
GROUP BY category_name;

This query is a bit more complicated because we had to join order_items with products and categories to get the category names used in the report.

Exercise

Run the template query and see how it generates a row with the history-to-date revenue for each category.