Subqueries in FROM and SELECT
Tables made on the fly.
Here's a question that needs two levels of grouping. On average, how many orders does each customer place? First you need orders per customer. Then the average of those numbers. You can't put an aggregate inside another aggregate. But you can use a subquery as a table.

- Average orders per customer
- Count, then average
- An aggregate of an aggregate
A subquery in FROM
The inner query counts orders for each customer. That gives a little table, with one row per customer. The outer query treats it like any other table, and takes the average and the maximum. Run it. Customers who order, place about three and a half orders on average. The most is eight.
SELECT ROUND(AVG(orders), 2) AS avg_orders,
MAX(orders) AS most_orders
FROM (SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id);โโโโโโโโโโโโโโฌโโโโโโโโโโโโโโ โ avg_orders โ most_orders โ โโโโโโโโโโโโโโผโโโโโโโโโโโโโโค โ 3.45 โ 8 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโโโ
A subquery in SELECT
A subquery can even be a column. Here, for every book, we subtract the average price, to see how far above or below average it is. Run it. Python for Curious Minds is almost seventeen dollars above average.
SELECT title,
price,
ROUND(price - (SELECT AVG(price) FROM books), 2) AS above_avg
FROM books
ORDER BY above_avg DESC
LIMIT 3;โโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโโ โ title โ price โ above_avg โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโโค โ Python for Curious Minds โ 34.99 โ 16.88 โ โ Data for Everyone โ 31.99 โ 13.88 โ โ SQL in a Weekend โ 29.99 โ 11.88 โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโโ
Summary
A subquery in from acts as a temporary table. A subquery in select adds a calculated column. Next, the cleverest kind of subquery.
Key takeaways- Subquery as a table
- Subquery as a column
- Aggregate of aggregates