Aggregate-then-join with subquery in FROM

A subquery in FROM works out totals first, like each customer's spend. Then you join that small result to another table, like names.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Total first, then join

SELECT c.full_name, t.total
FROM customers c
JOIN (SELECT customer_id, SUM(revenue) AS total FROM orders GROUP BY customer_id) t ON c.customer_id = t.customer_id

Result:

full_name	total
Ada Obi	200
Ben Cole	80
Cara Diaz	200

Step by step

This is the subquery: one row per customer, with their total.

SELECT customer_id, SUM(revenue) AS total FROM orders GROUP BY customer_id

Inside FROM or JOIN, the subquery acts like a table called t.

SELECT c.full_name, t.total
FROM customers c
JOIN (SELECT customer_id, SUM(revenue) AS total FROM orders GROUP BY customer_id) t

Match each customer to their total.

SELECT c.full_name, t.total
FROM customers c
JOIN (SELECT customer_id, SUM(revenue) AS total FROM orders GROUP BY customer_id) t ON c.customer_id = t.customer_id;

Practice

Try each one in your head first, then open the answer.

1. Show each customer's full_name and their total revenue as total, totalling orders first in a subquery.

SELECT c.full_name, t.total FROM customers c JOIN (SELECT customer_id, SUM(revenue) AS total FROM orders GROUP BY customer_id) t ON c.customer_id = t.customer_id

2. Show each customer's full_name and how many orders they placed, as order_count, counting in a subquery first.

SELECT c.full_name, t.order_count FROM customers c JOIN (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) t ON c.customer_id = t.customer_id

3. Show full_name and total for customers whose total revenue is over 150.

SELECT c.full_name, t.total FROM customers c JOIN (SELECT customer_id, SUM(revenue) AS total FROM orders GROUP BY customer_id) t ON c.customer_id = t.customer_id WHERE t.total > 150

4. Show each customer's full_name and their average order revenue as avg_order, averaging in a subquery first.

SELECT c.full_name, t.avg_order FROM customers c JOIN (SELECT customer_id, AVG(revenue) AS avg_order FROM orders GROUP BY customer_id) t ON c.customer_id = t.customer_id

5. Show full_name and total for every customer, highest total first.

SELECT c.full_name, t.total FROM customers c JOIN (SELECT customer_id, SUM(revenue) AS total FROM orders GROUP BY customer_id) t ON c.customer_id = t.customer_id ORDER BY t.total DESC

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed