Instruction
Perfect! Suppose we now want to show the total amount paid for each order alongside the amount paid for non-vegetarian products. Take a look:
Note: Non-vegetarian products have a category_id of 6 and 8.
SELECT
o.order_id,
SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS total_price,
SUM(CASE
WHEN p.category_id in (6, 8) THEN oi.quantity * oi.unit_price * (1 - oi.discount)
ELSE 0
END) AS non_vegetarian_price
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
JOIN products p
ON p.product_id = oi.product_id
GROUP BY o.order_id;
So far, we wrote the queries with SUM(CASE WHEN...) in such a way that they could all be replaced with equivalent COUNT(CASE WHEN...) constructions. Here, however, SUM(CASE WHEN...) is the only option – we want to sum certain values (oi.quantity * oi.unit_price * (1 - oi.discount)) instead of merely counting rows.
Exercise
This time, we want a report that will show each supplier alongside their number of units in stock and their number of expensive units in stock. Show four columns: supplier_id, company_name, all_units (all units in stock supplied by that supplier), and expensive_units (units in stock with a unit price over 40.0, supplied by that supplier).
Stuck? Here's a hint!
JOIN the products and suppliers tables. Group by supplier_id and company_name.
