0

This is my query:

SELECT AVG(user_reviews.rate) AS SQLAVG,facilities_stores.* 
FROM `facilities_stores` 
LEFT JOIN user_reviews ON user_reviews.facility_id = facilities_stores.id 
WHERE facilities_stores.id IN (17,20,21,35) 
GROUP BY facilities_stores.id ORDER BY SQLAVG DESC

If this is SUM or COUNT. This query working fine.

But when it is AVG not work.

This query working fine with Mysql < 5.6. I'm not sure what I'm wrong.

Look like at Mysql 5.7 have conflict of ORDER BY with GROUP BY

This is my result

enter image description here

Quynh Nguyen
  • 2,959
  • 2
  • 13
  • 27
  • what is your expected result? without sample data your result doesnt tell us anything. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza Sep 20 '16 at 15:49
  • You seem to have an incomplete `GROUP BY` clause, something that MySQL is too tolerant for by default. Most likely, your results in MySQL/5.6 where incorrect too, or they were correct by pure chance. – Álvaro González Sep 20 '16 at 15:56
  • @e4c5 I had try this but I got result is Null. – Quynh Nguyen Sep 20 '16 at 16:31
  • If the links in the close don't make sense let us know. @e4c5 recommended one that had an answer from Ollie. The other one had answers from Shadow and myself. – Drew Sep 20 '16 at 17:15

0 Answers0