78. Найти общее количество проданных книг по каждому автору
Условие задачи:
Даны две таблицы:
books_authors— информация о книгах и их авторах;sales_stores— информация о продажах книг в магазинах.
Необходимо вывести для каждого автора общее количество проданных экземпляров всех его книг.
Для подсчёта количества проданных книг необходимо использовать поле quantity.
Структура таблиц:
books_authors
book_id | title | author_name | country | genre
--------+---------------------------------+-------------------+----------------+---------
1 | Война и мир | Лев Толстой | Россия | Роман
2 | Анна Каренина | Лев Толстой | Россия | Роман
3 | Преступление и наказание | Фёдор Достоевский | Россия | Роман
4 | Идиот | Фёдор Достоевский | Россия | Роман
5 | Гарри Поттер и философский... | Джоан Роулинг | Великобритания | Фэнтези
6 | Гарри Поттер и Тайная комната | Джоан Роулинг | Великобритания | Фэнтези
7 | Оно | Стивен Кинг | США | Ужасы
8 | Сияние | Стивен Кинг | США | Ужасы
9 | Убийство в Восточном экспрессе | Агата Кристи | Великобритания | Детектив
10 | Десять негритят | Агата Кристи | Великобритания | Детектив
sales_stores
sale_id | book_id | store_name | city | quantity | sale_amount
--------+---------+--------------+-------------------+----------+------------
1 | 1 | Книжный рай | Москва | 3 | 2550
2 | 5 | Читай-город | Санкт-Петербург | 5 | 2750
3 | 7 | Буквоед | Москва | 2 | 1300
4 | 5 | Книжный рай | Москва | 4 | 2200
5 | 2 | Литера | Екатеринбург | 1 | 750
6 | 6 | Читай-город | Санкт-Петербург | 3 | 1740
7 | 8 | Книги и кофе | Новосибирск | 2 | 1240
8 | 9 | Буквоед | Москва | 4 | 1920
9 | 10 | Книжный рай | Москва | 2 | 1040
10 | 5 | Литера | Екатеринбург | 6 | 3300
11 | 3 | Читай-город | Санкт-Петербург | 1 | 680
12 | 7 | Книги и кофе | Новосибирск | 3 | 1950
13 | 1 | Буквоед | Москва | 2 | 1700
14 | 6 | Книжный рай | Москва | 4 | 2320
15 | 5 | Книги и кофе | Новосибирск | 7 | 3850
Спойлеры к решению
Подсказки
💡 Таблицы можно связать по полю book_id.
💡 Один автор может иметь несколько книг, а одна книга — несколько записей о продажах.
💡 После объединения таблиц необходимо сгруппировать данные по автору.
💡 Для получения общего количества проданных экземпляров используется SUM(quantity).
Решение
Соединим таблицы books_authors и sales_stores по идентификатору книги, после чего сгруппируем полученные строки по имени автора.
SELECT
ba.author_name,
SUM(ss.quantity) AS total_quantity
FROM books_authors ba
JOIN sales_stores ss
ON ss.book_id = ba.book_id
GROUP BY ba.author_name;
JOIN связывает каждую продажу с соответствующей книгой:
ON ss.book_id = ba.book_id
После объединения у каждой продажи становится доступно имя автора книги.
Далее строки группируются:
GROUP BY ba.author_name
и для каждой группы суммируется количество проданных экземпляров:
SUM(ss.quantity)
Для приведённых данных результат будет следующим:
author_name | total_quantity
-------------------+---------------
Лев Толстой | 6
Фёдор Достоевский | 1
Джоан Роулинг | 29
Стивен Кинг | 7
Агата Кристи | 6
Например, у Джоан Роулинг продажи складываются из двух книг.
Для книги с book_id = 5:
5 + 4 + 6 + 7 = 22
Для книги с book_id = 6:
3 + 4 = 7
Итого:
22 + 7 = 29
Если необходимо также вывести авторов, книги которых ещё ни разу не продавались, можно использовать LEFT JOIN и COALESCE:
SELECT
ba.author_name,
COALESCE(SUM(ss.quantity), 0) AS total_quantity
FROM books_authors ba
LEFT JOIN sales_stores ss
ON ss.book_id = ba.book_id
GROUP BY ba.author_name;
В таком случае автор без продаж также попадёт в результат, а количество проданных экземпляров для него будет равно 0.