Aggregating across joins

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.