Click here to Skip to main content
15,942,757 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have the following tables:

CREATE TABLE `country` (
  `name` VARCHAR(60) NOT NULL,
  `code` VARCHAR(3) UNIQUE NOT NULL,
  PRIMARY KEY  (`code`)
);

CREATE TABLE `user` (
  `userId` INT UNIQUE NOT NULL AUTO_INCREMENT,
  `country` VARCHAR(3) NOT NULL,
  `age` INT NOT NULL,
  PRIMARY KEY  (`userId`),
  CONSTRAINT `fk_user_country` FOREIGN KEY (`country`) REFERENCES `country`(`code`)
);

CREATE TABLE `bookRating` (
  `userId` INT NOT NULL,
  `isbn` VARCHAR(13) NOT NULL,
  `rate` INT NOT NULL,
  `date` DATE NOT NULL, 
  CONSTRAINT `fk_bookRating_user` FOREIGN KEY (`userId`) REFERENCES `user`(`userId`),
  CONSTRAINT `fk_bookRating_book` FOREIGN KEY (`isbn`) REFERENCES `book`(`isbn`)
);

CREATE TABLE `book` (
  `isbn` varchar(13) UNIQUE NOT NULL,
  `bookTitle` VARCHAR(280),
  `bookAuthor` VARCHAR(150),
  `yearPublication` int(4),
  -- `yearPublication` must be an integer because we have value less that 1901 in dataset
  `publisher` VARCHAR(135),
  PRIMARY KEY  (`isbn`),
  CONSTRAINT `publication_yea_chk` check ((`yearPublication` > -1) && (`yearPublication` < 2101))
);


As I am saying on the title I want to find the book with the highest average rating, For each country

What I have tried:

I have tried this query:


SELECT `country`.`name`,`bookRating`.`isbn` , AVG(`bookRating`.`rate`) FROM `country`
INNER JOIN `user` ON `country`.`code` = `user`.`country`
INNER JOIN `bookRating` ON `user`.`userId` = `bookRating`.`userId`
WHERE bookRating.isbn IN (
	SELECT `bookrating`.`isbn` from `bookrating`
	GROUP BY `bookrating`.`isbn`
)
GROUP BY `country`.`name`
ORDER BY AVG(`bookRating`.`rate`) DESC;


But I know it is not correct. How could I do it?

Am I trying this wrong from the start?
Posted
Updated 25-Feb-22 0:14am
Comments
Mohibur Rashid 24-Feb-22 23:23pm    
I am not sure if my solution is going to be an overkill but consider windowing function
https://mariadb.com/kb/en/window-functions-overview/
_Asif_ 25-Feb-22 5:15am    
it would have been much easier for us to solve this issue if you have provided sample data along with the expected result.

Take a look here: MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions[^]

MySql has already embedded ranking functions:
CUME_DIST() 	Cumulative distribution value
DENSE_RANK() 	Rank of current row within its partition, without gaps
NTH_VALUE() 	Value of argument from N-th row of window frame
NTILE() 	Bucket number of current row within its partition.
PERCENT_RANK() 	Percentage rank value
RANK() 	Rank of current row within its partition, with gaps
ROW_NUMBER() 	Number of current row within its partition


Use them!

A Guide to MySQL RANK Funtion By Practical Examples[^]
MySQL | Ranking Functions - GeeksforGeeks[^]
 
Share this answer
 
Comments
CPallini 25-Feb-22 3:22am    
5.
Maciej Los 25-Feb-22 3:28am    
Thank you, Carlo.
:)
Try this

SQL
DECLARE @country TABLE (
  name VARCHAR(60) NOT NULL,
  code VARCHAR(3) UNIQUE NOT NULL
);

DECLARE @User TABLE (
  userId INT UNIQUE NOT NULL IDENTITY,
  country VARCHAR(3) NOT NULL,
  age INT NOT NULL
);

DECLARE @book TABLE  (
  isbn varchar(20) UNIQUE NOT NULL,
  bookTitle VARCHAR(280),
  bookAuthor VARCHAR(150),
  yearPublication int,
  -- yearPublication must be an integer because we have value less that 1901 in dataset
  publisher VARCHAR(135)
);


DECLARE @bookRating TABLE (
  userId INT NOT NULL,
  isbn VARCHAR(20) NOT NULL,
  rate INT NOT NULL,
  date DATE NOT NULL
);


INSERT INTO @country (name, code) 
SELECT 'United States', 'US'
UNION ALL
SELECT 'United Kingdom', 'UK'
UNION ALL
SELECT 'Pakistan', 'PK'
UNION ALL
SELECT 'China', 'CH';

INSERT INTO @USER (country, age)
SELECT 'US', 20
UNION ALL
SELECT 'UK', 21
UNION ALL
SELECT 'CH', 22
UNION ALL
SELECT 'PK', 23
UNION ALL
SELECT 'US', 27
;

INSERT INTO @book (isbn, bookTitle, bookAuthor, yearPublication, publisher)
SELECT '978-1-933624-00-1', 'Book Title 1', 'Book Author 1', 2000, 'Publisher1'
UNION ALL
SELECT '978-1-933624-00-2', 'Book Title 2', 'Book Author 2', 2001, 'Publisher2'
UNION ALL
SELECT '978-1-933624-00-3', 'Book Title 3', 'Book Author 3', 2002, 'Publisher3'
UNION ALL
SELECT '978-1-933624-00-4', 'Book Title 4', 'Book Author 4', 2003, 'Publisher4'
UNION ALL
SELECT '978-1-933624-00-5', 'Book Title 5', 'Book Author 5', 2005, 'Publisher5'
UNION ALL
SELECT '978-1-933624-00-6', 'Book Title 6', 'Book Author 6', 2006, 'Publisher6'
UNION ALL
SELECT '978-1-933624-00-7', 'Book Title 7', 'Book Author 7', 2007, 'Publisher7'
UNION ALL
SELECT '978-1-933624-00-8', 'Book Title 8', 'Book Author 8', 2008, 'Publisher8'

INSERT INTO @bookRating 
SELECT 1, '978-1-933624-00-1', 2, '01-Jan-2000'
UNION ALL 
SELECT 5, '978-1-933624-00-1', 3, '01-Jan-2000'
UNION ALL 
SELECT 3, '978-1-933624-00-2', 4, '01-Jan-2001'
UNION ALL 
SELECT 3, '978-1-933624-00-2', 2, '01-Jan-2001'
UNION ALL 
SELECT 4, '978-1-933624-00-3', 2, '01-Jan-2002'
UNION ALL 
SELECT 4, '978-1-933624-00-3', 5, '01-Jan-2002'


select T1.name, T1.BookTitle, Rate
from	
(
	select C.Code, MAX(BR.rate) MAXRating
	from   @bookRating BR
			inner join @book B on BR.isbn = B.isbn
			INNER JOIN @USER U ON BR.UserID = U.USERId
			INNER JOIN @COUNTRY C ON U.country = C.Code
	group by C.Code
) T
inner join 
(
	select C.Code, C.name, B.BookTitle, BR.ISBN, BR.rate
	from   @bookRating BR
			inner join @book B on BR.isbn = B.isbn
			INNER JOIN @USER U ON BR.UserID = U.USERId
			INNER JOIN @COUNTRY C ON U.country = C.Code
) T1 ON T.Code = T1.Code AND T.MAXRATING = T1.RATE
 
Share this answer
 
Comments
Member 15439261 25-Feb-22 11:27am    
@_Asif This is a good answer but I think that it is not finding the book with the highest average rate.
It just finds the book with the highest rate for each country. How can I find the book with the highest average rate for each country?
_Asif_ 26-Feb-22 13:44pm    
How difficult it would be to modify the query to use avg() function if you have understood the solution.

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