Lesson 7 of 12 · 10 min

Joins with totals

Joins and grouping together answer the questions businesses actually ask: who are our best customers, which products sell, which city buys the most?

The value of an order line is its quantity times the product's price. That needs columns from two tables, so join first, then group:

SELECT p.category, SUM(o.quantity) AS items_sold
FROM orders AS o
JOIN products AS p ON p.id = o.product_id
GROUP BY p.category
ORDER BY items_sold DESC;

You can put expressions inside aggregates, like SUM(o.quantity * p.price), and order by the name you gave a column with AS.

Think of it in steps:

  1. FROM and JOIN build one wide table of order lines with all the facts.
  2. GROUP BY collects those lines into one bucket per customer.
  3. SELECT works out the summary for each bucket.
  4. ORDER BY sorts the buckets.

Your task

For each customer who has placed an order, show their name as customer and the total amount they have spent as total (quantity times price, summed, rounded to 2 decimals). Sort from the biggest spender down.

query.sql
-- Total spent per customer

SQLite runs right here in your browser. The first run downloads about 0.7 MB; after that it is instant.

Discussion

Stuck? Ask here. Be kind, and share hints rather than full answers.

Loading the discussion…