# Conditional rounding failling

In MySQL, I'm attempting the something like the following query but not getting the desired results:

``````SELECT ROUND(t.price / t.qty, IF(qty > 1, 4, 2)) AS unit_cost
FROM (
SELECT 0.10 AS price, 1 AS qty
UNION
SELECT 2.60 AS price, 25 AS qty
) t
``````

What I'd like is:

``````+-----------+
| unit_cost |
+-----------+
|  0.10     |
|  0.1040   |
+-----------+
``````

But for some reason the result is:

``````+-----------+
| unit_cost |
+-----------+
|  0.100000 |
|  0.104000 |
+-----------+
``````

Oddly, this works:

``````SELECT ROUND(0.10000, IF (1=1, 2, 4));
``````

So I know that conditional rounding is possible. How can I achieve the desired result set?

Try using FORMAT()

``````SELECT FORMAT(t.price / t.qty, IF(qty > 1, 4, 2)) AS unit_cost
FROM (
SELECT 0.10 AS price, 1 AS qty
UNION
SELECT 2.60 AS price, 25 AS qty
) t
``````
• (That is likely to LEFT justify the result, not RIGHT.) Jan 7 at 23:40
• @RickJames Look at his `What I'd like is:` output. It is left-justified. So, `FORMAT()` should do it Jan 8 at 0:11
• But the decimal points won't line up if some numbers are >= 10.0000 Jan 8 at 1:20
• Sorry if it was not clear but I don't care about justification of results, only the values. This does the trick. I'm still curious why`ROUND()` doesn't work though - if you care to add an explanation that would be great. Thanks Rolando! Jan 8 at 1:45

I think, without solid proof, that `ROUND()` ignores the second argument unless it is an integer literal.

Here is a workaround:

``````SELECT IF(qty > 1,
ROUND(t.price / t.qty, 4),
ROUND(t.price / t.qty, 2)
) AS unit_cost
FROM (
SELECT 0.10 AS price, 1 AS qty
UNION
SELECT 2.60 AS price, 25 AS qty
) t

+-----------+
| unit_cost |
+-----------+
|      0.10 |
|    0.1040 |
+-----------+
``````

If you need more than 2 cases, consider using a `CASE` expression.

Feel free to file a bug report at bugs.mysql.com, requesting that either `ROUND()` allow an expression as the second argument, or that the documentation state to the contrary.

• That's a good workaround Rick. I'm not sure about your hypothesis though - here's a conundrum: `SELECT ROUND(0.10000, IF (1=1, 2, 4))` and `SELECT ROUND(0.10000, IF (1=2, 2, 4))`. Both of those work when run on their own, disproving the integer literal idea. However - this does not: `SELECT ROUND(0.10000, IF (1=1, 2, 4)) UNION ALL SELECT ROUND(0.10000, IF (1=2, 2, 4))`. Seems this may have something to do with decimals of different lengths coexisting in the same column. Oddly, if that were the case, your workaround wouldn't work - which leaves me scratching my head. Jan 10 at 19:12
• @billynoah - I think I can explain those. The first pair contains expressions that are evaluated before running the query: `IF (1=2, 2, 4))` evaluates to `4`. The Union case probably determines the type (`DECIMAL(...,4)`) from the max precision of the Selects, then forces the others to agree. Jan 10 at 20:31
• (It's kinda fun to try to figure out what is going on, simply by looking at the results. It might be faster to find the code and read it.) These cases deserve documenting. Jan 10 at 20:35