SQL Query to find book sales using Joins and Group By

SQL Query to find total book sales that every author has made using Joins and Group By on Multiple Columns.

SQL Query to find total book sales that every author has made using Joins and Group By on Multiple Columns

We want to retrieve the below details:

1. List of all authors
2. Their books
3. The total sales every book has made by each author.

Table: [orders]

order_id	book_id	year	amount
1	101	2022	120
2	101	2022	120
3	102	2022	130
4	105	2022	150
5	105	2022	150
6	107	2022	200
7	101	2023	120
8	101	2023	120
9	105	2023	150
10	107	2023	200
11	108	2023	180

Table: [authors]

author_id	author_name
1	Jake
2	Peter
3	Laura

Table: [books]

book_id	book_name	author_id
101	How to be Happy	1
102	7 Happy Habits	1
105	Build a Good Body	2
107	Messages in Water	3
108	Love Languages	3

Sample Output

author_name	book_name	total_amount
Jake	How to be Happy	480
Jake	7 Happy Habits	130
Peter	Build a Good Body	450
Laura	Messages in Water	400
Laura	Love Languages	180

Solution

1. Join the tables and group them by the author_id and book_id. 
   • This will give a single row for every author-book combination in the orders table. 
2. Then run the sum() aggregate function over the amount column 
   • This will give the total sales for every author’s book.

select a.author_name, b.book_name, 
sum(o.amount) as total_amount
from orders o
join books b 
on b.book_id = o.book_id
join authors a
on a.author_id = b.author_id
group by a.author_id, a.author_name, b.book_id, b.book_name

Group By can be used in all scenarios where data needs to be grouped by some columns and aggregation is performed over other columns, resulting in reduced number of rows.