0

I am doing a webpage of a food review, there is a page called top 10 where it will display the top 10 according to food review. I currently have this SQL but it combines all the dishes ratings and combine as a 1 dish. This SQL statement is being placed in PHP code.

SELECT i.path, i.name, AVG(r.review_rating), r.image_id
  FROM images i, review r
 WHERE r.image_id = i.id
 GROUP BY 'r.image_id'
 ORDER BY AVG(r.review_rating) DESC
 LIMIT 10
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
user3567749
  • 13
  • 2
  • 8
  • Welcome to Stack Overflow. Please read the [About] page and [Ask] page soon. 'Not working' is not a good characterization of the problem. With SQL, we really need to see an outline schema for each table in the query, with a few (2-5?) rows of data, and a description of what you're seeing and what you wanted to see. It may be sufficient to simply give the table name, column names and sample values without a formal schema. – Jonathan Leffler Aug 17 '16 at 04:43
  • What are the quotes doing around `r.image_id` in the GROUP BY clause? They appear to be single quotes, so you are grouping by a constant string, which might mean you only get one group because there's only one value for the grouping 'column' (value). If you were using MySQL (it's a good idea to identify which DBMS you're using with SQL questions), you might be intending to use ``GROUP BY `r`.`image_id` `` though frankly I'd use `GROUP BY r.image_id`. – Jonathan Leffler Aug 17 '16 at 04:48
  • And the old-fashioned `FROM table1, table2` notation was rendered obsolete by SQL-92. You should not use it in SQL written 20+ years later. – Jonathan Leffler Aug 17 '16 at 04:50

2 Answers2

1

Without seeing your sql schema it is hard to know exactly what you need to change it to. But it looks like you might want a to use a JOIN instead of selecting from multiple tables. Try something this:

SELECT i.path,
    i.name,
    AVG(r.review_rating),
    r.image_id 
FROM images i
JOIN review r ON  r.image_id = i.id 
GROUP BY r.image_id 
ORDER BY AVG(r.review_rating) DESC 
LIMIT 10

You'll also need to check that there are actually 10 different images (10 or more unique r.image_id's. You can check this by removing the GROUP BY and looking at the result).

If that doesn't work then post your schema.

MrMadsen
  • 2,713
  • 3
  • 22
  • 31
1

The problem is on GROUP BY 'r.image_id'.
'r.image_id' is a string and this makes GROUP BY create a single group from all the rows filtered by the WHERE clause.

You probably want GROUP BY r.image_id.

Read more about when to use apostrophes, quotes and backticks in MySQL.

Community
  • 1
  • 1
axiac
  • 68,258
  • 9
  • 99
  • 134