Q:

MySQL round() not working as expected with sum()

I'm using the following query;
select round(sum(quantity * calories)) as calories
from tableName;

where quantity and calories are of type double and having the values 1.00 & 110.50 respectively. I'm expecting an output of 111, but the above query is instead giving me 110. But if I execute a simple query like the below,
select round (110.50);

It's giving me 111 as the output. I went through this Link, and used a query like the following;
select round(sum(cast(quantity * calories as char))) as calories
from tableName;

but this one also not working for me and giving 110 as output. Can someone tell how to arrive at the desired output of 111?

A:

When you do calculations with floating point numbers, it is better to use decimal(m,d) datatype rather than float / double as both float and double represent approximate numeric data values and are prone to rounding errors when used with calculations.
For your query you can use:
select round(cast(sum(quantity * calories) as decimal(8,1))) as calories
from tableName;

