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

You don't actually have to do the cast. The division produces a decimal, even when both arguments are integers. Also, nullif() is shorter (and more standard) than if():

select id, rating_sum / nullif(rating_count, 0) as average_rating 
from document d left join
     document_aggregate_rating dar
     using (id)
where id = 123;

Finally, it is unclear where the columns are coming from. But, if both come from document_aggregate_rating, you can phrase the query without a join:

select 123 as id, max(rating_sum) / nullif(max(rating_count), 0) as average_rating 
from document_aggregate_rating dar
where id in (123);

The aggregation guarantees that one row is returned, even when there are no matches.