Skip to the content
Start

A subquery as a table

SQL · Unit 9: Subqueries

How many orders come in on the busiest day? You already know how to count each day's orders, with GROUP BY placed. Keeping the highest of those counts would take MAX(COUNT(*)), and you can't put one aggregate inside another.

That hasn't changed. What you can do is summarize in two steps. The result of a query is a table, and a table can go in a FROM. The inner query leaves one row per day, with its count in a column called times. The outer one goes through those rows and takes the maximum.

SELECT MAX(times)
FROM (
  SELECT COUNT(*) AS times
  FROM orders
  GROUP BY placed
) per_day;

Returns

MAX(times)
2

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Put the lines in order

    How many products does a section have, on average? Put in order the query that first counts each section's products and then averages those counts. Two lines don't belong.

  2. 2. Predict the output

    Six customers have ordered something. Ann and Luke have ordered three times, Valerie and Dylan twice, and Chloë and Mason once. Hannah and Brian have never ordered. What does this query return?

  3. 3. Find the bug

    How many units does the most-ordered product add up to? The inner query adds up each product's units, and the outer one looks for the highest sum. Here SQL answers no such column: quantity. Which line has the problem?

  4. 4. Complete the code

    On which days did more than one order come in? Fill in the blank with the word that filters the rows of per_day.

  5. 5. Predict the output

    There were orders on nine days. On three of them, January 4, 12, and 15, two came in, and on the rest, one. This query reads from the inside out. What does it return?

  6. 6. Multiple choice

    Which of these questions can't be answered with a subquery in the WHERE and nothing else?

  7. 7. Put the lines in order

    Which customers have never gotten a big box, of 3 units or more? You've already answered this question with LEFT JOIN and with NOT IN. Put the third form in order. Two lines don't belong.

  8. 8. Find the case that fails

    The store is telling everyone who has ever ordered a Keyboard, product 1, about a new model. Each customer has to be on the list only once. They're Ann and Valerie. Someone wrote it with a JOIN. Which of these changes puts a name on the list twice?

  9. 9. Write the code

    How many customers have come back to buy again? Write a query that returns a single number, how many customers have more than one order. First you need the table of those customers, and then you count its rows.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →