mysql rounddown_Is there a ROUNDDOWN() function in sql as there is in EXCEL

可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:

问题:

Say I have a table which has two columns i.e. Quantity and Percentages where my percentages are in decimals. Now I want to multiply these two columns and Round the value down to 2 decimals. Rounding down here means that all the numbers from 1-9 are rounded down. Is there an inbuilt function in SQL to do so as there is in Excel?

Examples:

13.567 should round to 13.56

136.7834 should round to 136.78

0.7699 should round to 0.76

I have tried searching for such a function online but couldn't come across an appropriate solution.

回答1:

You can use TRUNCATE () for this rounddown

select TRUNCATE(2.847, 2) as rounddown

or

SELECT Floor(135.675); //for integer rounding, like 135

You can also use

select round(123.456, 2, 1) as rounddown

The 3rd parameter being non-zero will cause a truncation after the number of decimal points specified in the 2nd parameter.

回答2:

There's a FLOOR function, which can be adapted to your use case:

SELECT FLOOR(value * 100) / 100 AS RoundedValue

回答3:

The solution to the problem is to truncate the extra decimal which can be achieved by using the extra parameter of the ROUND function which is ROUND(number, decimal_places, 0/1). Here if the last parameter is anything other than 0, it will truncate the rather than rounding off which is equivalent to the ROUNDDOWN() function of excel that I was looking for.

Alternatively, you can use the TRUNCATE() function, passing the number of decimal places to keep as the second parameter, which will drop off any extra decimals, acting as a ROUNDDOWN() function.

回答4:

yes there are some Function in sql for round

ex:

SELECT ProductName, Price, FlOOR(Price) AS RoundedPrice

FROM Products;