0

I have this line of query here

if (type = 1,

(IF(ISNULL(users), 
'', 
((SUM(actual) / 1) * 0.04/12) * if(users = "user", booked/(36/12),'')
))

,
'false')

the example output is 123.3333333

I want it to output just 2 decimals so it'll be like 123.33 where should I place the Truncate or Round command?

Thank You!

hearmeroar
  • 297
  • 1
  • 8
  • 18
  • Maybe this can help you: http://stackoverflow.com/questions/13541650/mysql-decimal-round?rq=1 – emco Apr 14 '14 at 13:51
  • Possible duplicate of http://stackoverflow.com/questions/11190668/format-number-to-2-decimal-places – dikesh Apr 14 '14 at 13:51

2 Answers2

2

How about:

if (type = 1,
(IF(ISNULL(users), '', ROUND(((SUM(actual) / 1) * 0.04/12) * if(users_0.user_name = "user", booked/(36/12),''),2)))
, 'false')
Franzl
  • 719
  • 1
  • 6
  • 13
1

You can use truncate()

TRUNCATE(123.3333333, 2) = 123.33


if (type = 1,
(
  IF(ISNULL(users), '', 
  TRUNCATE(
  (
    (SUM(actual) / 1) * 0.04/12) * if(users_0.user_name = "user", booked/(36/12),'')
  ),2)

)

,
'false')
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • @hearmeroar what syntax error? Did you try fixing it yourself? You should be more helpful to the person who is helping you. – djechlin Apr 14 '14 at 14:03
  • 1
    sorry about that I added the truncate function in a wrong place.. to many parentheses confused me. Just corrected it. – Abhik Chakraborty Apr 14 '14 at 14:03