AI Hints
Homeโ€บSQL Tutorialโ€บSubqueries
SQL ยท Lesson 56 of 126

Subqueries in FROM and SELECT

Tables made on the fly.

โฑ 2 min readโ–ถ Video at 1:20:16๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Subqueries in FROM and SELECT
From the video: Subqueries in FROM and SELECT
  • 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.

query.sqlsql
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);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
SELECT title,
       price,
       ROUND(price - (SELECT AVG(price) FROM books), 2) AS above_avg
FROM books
ORDER BY above_avg DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          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
Next up: Correlated Subqueries โ€” a subquery for every row