Aggregating across joins

Join, then group — the reporting workhorse.

Join then summarize

The most common real query joins tables and then groups. Revenue per order = sum of quantity × unit_price over its line items:

SELECT o.id, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id
ORDER BY o.id;

The join builds the wide row set; GROUP BY collapses it back down per order.

The join widens the data to one row per line item; the GROUP BY narrows it back to one row per order. Aggregate after the join, never before it.