MySql show,count and descending

Question

I'm having a problem with this statement to execute in mysql, could you maybe help me? the question is; For EVERY book in the book table, show the title, the count of the number of ratings, the minimum rating, the maximum rating, and the average rating rounded to the nearest penny. Your results should be sorted by the count of the number of ratings in descending order, followed by the average ratings in descending order. Your output should match the display given below (including column headings):

the diagram1 the actual outcome table 2

This sounds like a homework question. What have you tried so far? — John Douthat, Nov 30 '15 at 22:20
@JohnDouthat, hello, yes it is an assignment which the professor gave me to try to solve, since I was looking for an "advance" sql topic, to be honest I still dont have anything and clue what to use properly in this task since this is material which I didnt covered in class yet, maybe a hint or something will be fine for a start — Amir Sumic, Nov 30 '15 at 23:19

score 0 · Answer 1 · edited May 23 '17 at 10:27

Here are some pointers that may help you:

You should probably check out MySQL's GROUP BY (Aggregate) Functions, specifically COUNT, for the number of ratings, and MIN, MAX, and AVG, for the ratings values.

The question asks you to match the column headings. You can make sure the names of the columns match the questions exactly by using quoted column aliases, like this:

SELECT SomeExpression AS `Total Ratings`

The question also asks you to return ALL books, not just those with ratings. You will probably want to use a LEFT JOIN rather than the default (INNER) JOIN. Here's a reference of the JOIN types.

MySql show,count and descending

1 Answers1