Запрос к результату

В конце первого урока вы читали, что результат запроса — тоже таблица. Значит, к нему можно обращаться с запросом: поставьте подзапрос в FROM, туда, где стояло бы имя таблицы.

Сколько в среднем стоит заказ? В таблице order_items есть строка на каждую игру в заказе, поэтому сначала нужна сумма каждого заказа:

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

Потом — среднее из этих сумм. AVG(SUM(quantity * price)) не работает, потому что агрегатные функции нельзя вкладывать друг в друга: получится misuse of aggregate function SUM(). Но можно вычислить суммы в подзапросе, а их среднее — во внешнем запросе:

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;

Подзапрос выполняется первым и создаёт временную таблицу из 30 строк, по одной на заказ, со столбцами order_id и total. Внешний запрос обращается с ней как с любой другой таблицей: может фильтровать её строки, группировать, вычислять по ним. Он ничего не знает про order_items; он видит только столбцы подзапроса под теми именами, которые дал им подзапрос. Поэтому у каждого вычисляемого столбца подзапроса должен быть псевдоним.

После закрывающей скобки идёт имя таблицы, здесь — AS totals. SQLite его не требует, а большинство других баз данных требуют, и оно нужно всякий раз, когда вы обращаетесь к столбцам подзапроса, например в соединении. Да, подзапрос можно соединять и с другими таблицами. Вот три самых больших заказа с датами из 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;

Типичная ошибка: использовать столбец, которого подзапрос не возвращает. Внешний запрос видит только order_id и total, поэтому price или quantity в нём заканчиваются ошибкой no such column.