Querying a Result

At the end of the first lesson, you read that the result of a query is a table too. That means you can query it: put a subquery in FROM, where the name of a table would be.

How much is an average order worth? The table order_items has a row for each game of an order, so first, you need the total of each order:

SELECT order_id, SUM(quantity * price) AS total
FROM order_items
GROUP BY order_id;

Then the average of these totals. AVG(SUM(quantity * price)) doesn't work, because aggregate functions can't be nested: it ends with misuse of aggregate function SUM(). But you can compute the totals in a subquery and their average in the outer query:

SELECT ROUND(AVG(total), 2) AS average_order
FROM (
    SELECT order_id, SUM(quantity * price) AS total
    FROM order_items
    GROUP BY order_id
) AS totals;

The subquery runs first and makes a temporary table of 30 rows, one for each order, with the columns order_id and total. The outer query treats it like any other table: it can filter its rows, group them, compute with them. It knows nothing about order_items; it only sees the subquery's columns, under the names the subquery gave them. That's why every computed column of the subquery needs an alias.

After the closing parenthesis comes the table's name, here AS totals. SQLite doesn't insist on it, but most other databases do, and you need it whenever you refer to the subquery's columns, for example in a join. Yes, you can join a subquery with other tables too. These are the three biggest orders, with their dates from orders:

SELECT o.id, o.ordered_at, t.total
FROM (
    SELECT order_id, SUM(quantity * price) AS total
    FROM order_items
    GROUP BY order_id
) AS t
JOIN orders AS o ON o.id = t.order_id
ORDER BY t.total DESC, o.id
LIMIT 3;

Typical mistake: using a column the subquery doesn't return. The outer query sees only order_id and total, so price or quantity in it ends with no such column.