Click here to Skip to main content
14,840,811 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have 3 tables : Author, Book and Sales with columns as below. I need to find the highest and lowest selling books (that had at least one sale) by quantity.

Table 1 : Author

| id | Author | Birth_year |
|----------------------------
| 1 | Smith | 2000
| 2 | Jack | 2001
| 3 | Carol | 2002
| 4 | Dave | 2003
Table 2 : Book

| id | Title | Published_year | Author_id |
|---------------------------------------------------------
| 1 | wall street | 1995 | 4
| 2 | wealth management | 1990 | 2
| 3 |Smart Tips for Success | 1985 | 1
| 4 | Keep Moving | 1980 | 3
| 5 | Fly or Die | 1975 | 1
Table 3 : Sales

| id | Sales_dt | book_id | Sales_price |
|---------------------------------------------------------
| 1 | 12-jan-2020 | 1 | 15
| 2 | 12-jan-2020 | 2 | 9
| 3 | 13-jan-2020 | 1 | 15
| 4 | 14-jan-2020 | 1 | 15
| 5 | 15-jan-2020 | 4 | 12

What I have tried:

Given these 3 tables without quantity column. How to order the query by quantity? Also how to check revenue as the question also states: If there are multiple possible books with the same quantity or revenue, sort by author ID and then by publishing year. Please help
Posted
Updated 8-Feb-21 19:58pm

1 solution

Start with ORDER BY: SQL ORDER BY Keyword[^] and JOIN: SQL Joins[^]
It's pretty easy when you get the hang of it:
SQL
SELECT c.CustomerName, o.OrderQuantity
FROM Customers c
JOIN Orders o
   ON o.CustomerID = c.ID
ORDER BY o.OrderValue DESC
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900