CODEWITHSUNDEEP
mysqlsqlsql-order-by
Shihas
Shihas

Reputation: 814

MySQL ORDER BY decimal value not working

The SQL query with order by clause is not working properly. 

VALUES:
5.0000   
4.0000   
4.5000  
4.7500  
4.0000  
5.0000

Query:

SELECT  AVG(rating) as rating
FROM review
GROUP BY id
ORDER BY CAST(`rating` as signed) DESC

When I execute this query, the output is: 

5.0000  
4.7500  
4.5000  
5.0000  
4.0000  
4.0000

My rating column is int(10).

Upvotes: 0

Views: 5353

Answers (1)

Luca Rainone
Luca Rainone

Reputation: 16458

You have to order by AVG(rating) not by rating

SELECT AVG(rating) as rating FROM review GROUP BY id ORDER BY AVG(rating) DESC

In your case, alias and column have the same name. In order to avoid confusion, you can do:

SELECT AVG(rating) as avgrating FROM review GROUP BY id ORDER BY avgrating DESC

Upvotes: 3

Related Questions