CODEWITHSUNDEEP
mysqlsql
Whiteboard
Whiteboard

Reputation: 91

Mysql : Use IF in TRUNCATE

This is the query, simplified.

SELECT `a`, TRUNCATE(`b` / 1000, 3) AS `b`
FROM (
    ...                         
) AS `m`
GROUP BY `a`
ORDER BY `a`

What i'm trying to do is change the number of decimal places (actual 3) based on the value of b.

So i tried this: 

SELECT `a`, TRUNCATE(`b` / 1000, IF(`b` < 10, 2, 3)) AS `b` ...

and this

SELECT `a `, IF(`b ` < 10, TRUNCATE(`b ` / 1000, 2), TRUNCATE(`b ` / 1000, 3)) AS `b `

If b is less than 10, i want 3 decimal places, otherwise 2.

But this doesn't seem to work ...

Resources : https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#function_if

Upvotes: 1

Views: 159

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

just change the values position that you put in your query

 SELECT `a `, IF(b  < 10, TRUNCATE(b  / 1000, 3), TRUNCATE(b  / 1000, 2))

AS b

if(a<1,2,3) means if a<1 then 2 will come as a value in your result so you have to switch your values position

use round

SELECT a , IF(b  < 10, round((b  / 1000), 2), round((b  / 1000), 3) ) AS b

The ROUND() function rounds a number to a specified number of decimal places.

example SELECT ROUND(345.156, 2); result = 345.16

     SELECT ROUND(345.156, 2);  result = 345.156

If you don't want round then TRUNCATE will shown 0.00 in case of b value less than 10, so what do you mean by not working ?

You need 3 decimal place when b<10 so you have to change the position of yours query result

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You have misplaced the order of queries to run, in case of true/false evaluation in If(). Following may work:

SELECT `a `, 
        IF(`b ` < 10, 
           TRUNCATE(`b ` / 1000, 3), 
           TRUNCATE(`b ` / 1000, 2)
          ) AS `b `

Upvotes: 1

Related Questions