1

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?

0
1

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
4
  • (That is likely to LEFT justify the result, not RIGHT.)
    – Rick James
    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
    – Rick James
    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 whyROUND() doesn't work though - if you care to add an explanation that would be great. Thanks Rolando!
    – billynoah
    Jan 8 at 1:45
1

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.

3
  • 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.
    – billynoah
    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.
    – Rick James
    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.
    – Rick James
    Jan 10 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.