Сравнение со средним
Сколько в школе оценок выше среднего балла? Так написать нельзя:
SELECT COUNT(*) FROM grades WHERE grade > AVG(grade);
SQLite ответит ошибкой misuse of aggregate: AVG(). Агрегатные функции нельзя использовать в WHERE.
Причина в том, что WHERE проверяет каждую строку по отдельности, а среднее считается по всем строкам сразу. Подзапрос решает задачу: он один раз считает среднее, а внешний запрос сравнивает с ним каждую оценку.
SELECT COUNT(*) AS above_avg
FROM grades
WHERE grade > (SELECT AVG(grade) FROM grades);
Результат:
Скалярный подзапрос можно поставить и в список столбцов. Выведем оценки Анны Смирновой рядом со средним баллом всей школы:
SELECT grade,
(SELECT ROUND(AVG(grade), 2) FROM grades) AS school_avg
FROM grades
WHERE student_id = 1;
Результат:
| grade | school_avg |
| 5 | 3.96 |
| 4 | 3.96 |
| 5 | 3.96 |
| 5 | 3.96 |
| 5 | 3.96 |
| 5 | 3.96 |