Calculating Total Sales by Product Category

"What's our total revenue by product category?" Every business asks this question eventually. It's the kind of query that combines JOINs and aggregates -- two core SQL skills working together.

The query

SELECT
    p.category_name AS product_category,
    SUM(od.unit_price * od.quantity) AS total_sales
FROM
    orders o
JOIN
    order_details od ON o.order_id = od.order_id
JOIN
    products p ON od.product_id = p.product_id
GROUP BY
    p.category_name

What's happening

Three tables are involved:

1. orders -- Each row is an order
2. order_details -- Each row is a line item (product, quantity, price)
3. products -- Each row has product info including category

The JOINs connect them: orders to their line items, line items to their product details.

SUM(od.unit_price * od.quantity) calculates revenue per line item and sums it. GROUP BY p.category_name groups those sums by category.

Why this matters

This pattern -- JOIN multiple tables, compute an aggregate, GROUP BY a dimension -- is the foundation of business reporting. Swap "category" for "region", "sales rep", or "month" and you've got a different report using the same structure.

Watch out for: Multiplied rows. If your JOINs create unexpected duplicates (because of one-to-many relationships), your SUM will be inflated. Always check row counts after JOINs to make sure they match your expectations.