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

Excellent! Now, we want to show the total revenue (as a reference point) next to the revenue of each category. We can use the following trick:

WITH all_categories AS (
  SELECT SUM(amount) AS total_sum
  FROM orders
)
SELECT 
  category_name,
  SUM(oi.amount) AS category_revenue,
  ac.total_sum
FROM all_categories ac, 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
GROUP BY category_name, ac.total_sum;

The new construction you can see is called a Common Table Expression (CTE). It creates a temporary table named all_categories that has a single column named total_sum. In the outer query, we add this temporary table to the FROM clause. We don't need a JOIN because we're separating all_categories from order_items with a comma. This indicates they are two different tables and they cannot be joined for a given column. Separating two tables with a comma means that each row from the first table will be joined with each row from the second table. In our case, all_categories contains only a single row, so that row will be added to each row of order_items before any other joins are made.

We can then add the total_sum column from all_categories as the third column in the SELECT clause. We also add total_sum in the GROUP BY clause.

Exercise

Create a report that shows the category revenue values for all orders placed in 2017. Show three columns: category_name, category_revenue, and total_sum. The last column should show the total revenue from 2017 for all rows.

Stuck? Here's a hint!

Add a WHERE clause in the CTE and in the outer query.