Find the book/books with the lowest average rating. Select book title and average rating-CodePudding

You are given three tables:

create table books (id int, title varchar(250), year int, author varchar(250));
create table reviewers(id int, name varchar(250));
create table ratings (reviewer_id int, book_id, rating int, rating_date date);

data

insert into books (id, title, year, author) values (101,    "A Tale Of Two Cities", 1859,   "Charles Dickens");
insert into books (id, title, year, author) values (102,    "The Lord of the Rings",    1955,   "J. R. R. Tolkien");
insert into books (id, title, year, author) values (103,    "The Hobbit",   1937,   "J. R. R. Tolkien");
insert into books (id, title, year, author) values (104,    "The Little Prince",    1943,   "Antoine de Saint-Exupry");
insert into books (id, title, year, author) values (105,    "Harry Potter and the Philosopher's Stone", 1997,   "J. K. Rowling");
insert into books (id, title, year, author) values (106,    "And Then There Were None", 1939,   "Agatha Christie");
insert into books (id, title, year, author) values (107,    "Dream of the Red Chamber", 1791,   null);
insert into books (id, title, year, author) values (108,    "She: A History of Adventure",  1887,   "H. Rider Haggard");

Reviewers

insert into reviewers (id, name) values (15201, "Joe Martinez");
insert into reviewers (id, name) values (53202, "Alice Lewis");
insert into reviewers (id, name) values (44203, "John Smith");
insert into reviewers (id, name) values (41204, "Mike Anderson");
insert into reviewers (id, name) values (66205, "Chris Thomas");
insert into reviewers (id, name) values (23206, "Elizabeth Black");
insert into reviewers (id, name) values (24407, "Jack Green");
insert into reviewers (id, name) values (25208, "Mike White");

Ratings

insert into ratings (reviewer_id, book_id, rating, rating_date) values (15201,  101,    2,  2015-02-11);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (15201,  101,    4,  2015-06-16);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (53202,  106,    4,  null);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (44203,  103,    2,  2015-01-12);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (44203,  108,    4,  2015-04-03);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (44203,  108,    2,  2015-01-23);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (41204,  101,    3,  2015-02-09);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (66205,  103,    3,  2015-01-26);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (66205,  104,    2,  2015-03-22);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (66205,  108,    4,  null);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (23206,  107,    3,  2015-01-15);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (23206,  106,    5,  2015-03-19);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (24407,  107,    5,  2015-05-20);
insert into ratings (reviewer_id, book_id, rating, rating_date) values (25208,  104,    3,  2015-09-02);

Find the book/books with the lowest average rating. Select book title and average rating.

Expected results

The Hobbit          2.5000
The Little Prince   2.5000

but my query is:

select books.title, avg(ratings.rating) as avg_rating from ratings join books on ratings.book_id = books.id
group by books.title
order by avg_rating desc;

My result, but is wrong

what's wrong in my query,

the lowest rating is: Expected results Expected results

CodePudding user response：

If you want the lowest you just need to use asc instead of desc:

select books.title, avg(ratings.rating) as avg_rating from ratings join books on ratings.book_id = books.id
group by books.title
order by avg_rating asc;

You want your result to increase not decrease. If you want to limit to the 2 lowest ratings you can add limit 2 after the order by clause

CodePudding user response：

I got it

select b.title, avg(rating) as avg_rating from ratings r
   join books b on
   r.book_id = b.id

group by b.title
having avg(rating) =
  (select min(avg_rating)
    from
      (select b.title, avg(rating) as avg_rating
      from ratings r
      join books b on r.book_id = b.id
      group by b.title
       )as t


    );

CodePudding user response：

Create view

create view rating_avg
as
select title, avg(rating) as rate 
from ratings join books on book_id = id 
group by title

Run

select title, rate from rating_avg where rate = (select min(rate) from rating_avg)