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:
SELECT
O.OrderID,
SUM(Quantity * OI.UnitPrice) AS TotalPrice,
SUM(CASE
WHEN P.CategoryID in (6, 8) THEN Quantity * OI.UnitPrice
ELSE 0
END) AS NonVegetarianPrice
FROM Orders O
JOIN OrderItems OI
ON O.OrderID = OI.OrderID
JOIN Products P
ON P.ProductID = OI.ProductID
GROUP BY O.OrderID;
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.UnitPrice) 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: SupplierID, CompanyName, AllUnits (all units in stock supplied by that supplier), and ExpensiveUnits (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 SupplierID and CompanyName.
