Question

1. Write a SELECT statement that answers this question: What is the total quantity purchased for each instrument within each category? Return these columns:

The category_name column from the category table

The instrument_name column from the instruments table

The total quantity purchased for each instrument with orders in the order_instruments table

Use the WITH ROLLUP operator to include rows that give a summary for each category name as well as a row that gives the grand total.
Use the IF and GROUPING functions to replace null values in the category_name and instrument_name columns with literal values if they’re for summary rows.

Database Schema:
------------------------------
Tables: musicians, orders, order_instruments, instruments, categories

Table columns
-----------------------
musicians: musician_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id

orders: order_id, musician_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id

order_instruments: item_id, order_id, instrument_id, item_price, discount_amount, quantity

instruments: instrument_id, category_id, instrument_code, instrument_name, description, list_price, discount_percent, date_added

categories: category_id, category_name

Answer 1

Below is the query required where group by is used to get sum of order quantity and rollup function to get the aggregation of all the sum of quantity for each category and instrument.

CASE WHEN is used as if condition to place literals 'category order qty' and 'instrument order qty' in place of null in the last row of aggregation.

SELECT CASE WHEN cat.category_name IS NULL
THEN 'category order qty'
ELSE cat.category_name
END,
CASE WHEN instr.instrument_name IS NULL
THEN 'instrument order qty'
ELSE instr.instrument_name
END,
SUM(ord_indtr.quantity)
FROM categories cat
INNER JOIN instruments instr ON cat.category_id = instr.category_id
INNER JOIN order_instruments ord_indtr ON instr.instrument_id = ord_indtr.instrument_id
GROUP BY ROLLUP(cat.category_name, instr.instrument_name)