Back-To-School Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Custom classifications of business objects
Custom grouping of business objects
Custom counting of business objects
14. Summing of business values
Summary

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.