You have an error in your SQL syntax...near 'float) / CAST(rating_count AS float)) as average_rating from document'

Question

Following is the query

select id, IF(rating_count = 0, null, CAST(rating_sum AS float) / CAST(rating_count AS float)) as average_rating 
from document d left join document_aggregate_rating using (id) where id in (123);

and error I am getting

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'float) / CAST(rating_count AS float)) as average_rating from document' at line 1

I can't figure out why is it giving syntax error.

Answer 1

Try like this:

select id, IF(rating_count = 0, null, 
CAST(rating_sum AS DECIMAL(10,6)) / CAST(rating_count AS DECIMAL(10,6))) as average_rating 
from document d left join document_aggregate_rating 
using (id) where id in (123);

The MySQL documentation says:

The type for the result can be one of the following values:

• BINARY[(N)]
• CHAR[(N)]
• DATE
• DATETIME
• DECIMAL[(M[,D])]
• SIGNED [INTEGER]
• TIME
• UNSIGNED [INTEGER]