Multi-Table Aggregation: 3–4 Table Joins with GROUP BY

The exam's signature query shape

Most Part B query marks come from one recurring pattern: walk a chain of 3–4 tables along their PK–FK links, filter, then aggregate per group. The sales chain in comp378 is the standard one:

customers ──< orders ──< order_lines >── products
          customer_id   order_id      product_id

Each < / > is a one-to-many link you join across. The reasoning is always the same three steps: (1) name every table you need to reach the columns in your SELECT and WHERE; (2) write the $n-1$ join conditions that chain them; (3) decide the grouping column(s) and which measure to aggregate.

The GROUP BY rule that the parser enforces

Every column in the SELECT list must be either inside an aggregate (SUM, COUNT, …) or listed in GROUP BY. Postgres rejects SELECT c.name, SUM(ol.qty) unless c.name is in the GROUP BY — there is no single value of name per group otherwise. Memorize: *grouped columns + aggregates only.*

Worked example: units of apparel per customer (exam 6-58 shape)

*Question:* for each customer who bought an apparel product, show the customer and the total units they bought.

Columns needed: customers.name (display + group), order_lines.qty (the measure), products.line (the filter). That forces all four tables into the join:

SELECT c.name AS customer, SUM(ol.qty) AS units
FROM   customers c
JOIN   orders o       ON o.customer_id = c.customer_id
JOIN   order_lines ol ON ol.order_id   = o.order_id
JOIN   products p     ON p.product_id  = ol.product_id
WHERE  p.line = 'apparel'
GROUP BY c.name
ORDER BY units DESC;

Four tables → three join conditions. WHERE p.line = 'apparel' filters to apparel order-lines *before* grouping; GROUP BY c.name makes one row per customer; SUM(ol.qty) totals their apparel units. Result, biggest first: Bookstore West 95, Alumni Office 30, Residence Hall 8.

Grouping by two columns

Group by more than one column to get a row per *combination*. *Question (exam 6-63):* for each salesperson and product line, the total quantity sold.

SELECT e.name AS salesperson, p.line, SUM(ol.qty) AS total_qty
FROM   employees e
JOIN   orders o       ON o.sold_by    = e.emp_id
JOIN   order_lines ol ON ol.order_id  = o.order_id
JOIN   products p     ON p.product_id = ol.product_id
GROUP BY e.name, p.line
ORDER BY e.name, p.line;

The sold_by foreign key in orders is what links a sale to the employee who made it. Grouping by (salesperson, line) yields one row per salesperson-and-line pair.