By LaBracca


2012-07-30 09:51:43 8 Comments

I would like to specify the number of decimal digits when I do a division between 2 integer numbers like:

select 1/3

That currently returns 0. I would like it to return 0,33.

Something like:

select round(1/3, -2)

But that doesn't work. How can I achieve the desired result?

8 comments

@Trouble Zero 2016-01-24 09:41:45

Might be a little late, but realize that CASTing to FLOAT is not allowed in MySQL and will raise an error when you attempt to CAST(1 AS float) as stated at MySQL dev.

The workaround to this is a simple. Just do

(1 + 0.0)

Then use ROUND to achieve a specific number of decimal places like

ROUND((1+0.0)/(2+0.0), 3)

The above SQL divides 1 by 2 and returns a float to 3 decimal places, as in it would be 0.500.

One can CAST to the following types: binary, char, date, datetime, decimal, json, nchar, signed, time, and unsigned.

@markusk 2017-09-04 18:45:00

MySQL does allow CAST, it just doesn't allow casting to FLOAT. Cast to DECIMAL instead. See e.g. stackoverflow.com/questions/7368163/….

@trojan 2017-03-08 10:33:21

If you came here (just like me) to find the solution for integer value, here is the answer:

CAST(9/2 AS UNSIGNED)

returns 5

@nazmul.3026 2017-02-13 04:22:44

Use this

select cast((1*1.00)/3 AS DECIMAL(16,2)) as Result

Here in this sql first convert to float or multiply by 1.00 .Which output will be a float number.Here i consider 2 decimal places. You can choose what you need.

@M.S. 2015-06-04 08:55:51

It's not necessary to cast both of them. Result datatype for a division is always the one with the higher data type precedence. Thus the solution must be:

SELECT CAST(1 AS float) / 3

or

SELECT 1 / CAST(3 AS float)

@Troglo 2015-02-11 15:32:27

Looks like this trick works in SQL Server and is shorter (based in previous answers)

SELECT 1.0*MyInt1/MyInt2

Or:

SELECT (1.0*MyInt1)/MyInt2

@EliSherer 2018-10-17 17:49:42

I liked this the most, it even looks nice for percentage calculations: SELECT 100.0 * @Elapsed / @Total

@Richard 2012-07-30 12:05:44

The suggestions from stb and xiowl are fine if you're looking for a constant. If you need to use existing fields or parameters which are integers, you can cast them to be floats first:

SELECT CAST(1 AS float) / CAST(3 AS float)

or

SELECT CAST(MyIntField1 AS float) / CAST(MyIntField2 AS float)

@LaBracca 2012-07-30 13:05:43

SELECT ROUND(CAST(1 AS float) / CAST(3 AS float), 2) thanks

@Tim 2014-03-20 14:05:35

Or you can cast them as decimals, if you want a decimal result.

@Troglo 2015-02-11 15:33:13

SELECT 1.0*MyInt1/MyInt2

@Trouble Zero 2016-01-24 09:54:15

CASTing might not work in MySQL.

@itsazzad 2016-02-09 05:38:45

@TroubleZero for MySQL use decimal

@Yasin Yörük 2016-02-11 11:38:43

thanks a lot, you saved my day. I was tried CAST(7/3 AS float) but returned 2 again. So cast numbers is solved my problem.

@robotik 2016-07-19 14:02:55

no need to cast both dividend and divisor, see @M.S.' answer stackoverflow.com/a/30639343/2866644

@SovietFrontier 2018-12-05 20:49:27

Anyone who is looking for help for DB2, this will work. For whatever reason, DECIMAL() does not work properly.

@xiaowl 2012-07-30 09:55:53

Because SQL Server performs integer division. Try this:

select 1 * 1.0 / 3

This is helpful when you pass integers as params.

select x * 1.0 / y

@hobailey 2016-12-15 17:24:28

Nice, simple and working answer, thank you!

@John 2017-03-06 11:34:30

You can even leave out the zeroes.

@stb 2012-07-30 09:55:21

use

select 1/3.0

This will do the job.

Related Questions

Sponsored Content

23 Answered Questions

[SOLVED] Limiting floats to two decimal points

11 Answered Questions

[SOLVED] How can I round a number in JavaScript? .toFixed() returns a string?

12 Answered Questions

[SOLVED] Formatting Decimal places in R

  • 2010-08-09 19:57:41
  • Brandon Bertelsen
  • 416421 View
  • 223 Score
  • 12 Answer
  • Tags:   r formatting rounding

7 Answered Questions

[SOLVED] Why does integer division in C# return an integer and not a float?

  • 2012-06-01 13:32:44
  • BanditoBunny
  • 139219 View
  • 112 Score
  • 7 Answer
  • Tags:   c# division

29 Answered Questions

[SOLVED] Formatting a number with exactly two decimals in JavaScript

12 Answered Questions

[SOLVED] How do you round a floating point number in Perl?

10 Answered Questions

[SOLVED] How can I round down a number in Javascript?

29 Answered Questions

[SOLVED] How to round a number to n decimal places in Java

7 Answered Questions

6 Answered Questions

[SOLVED] How to round down to nearest integer in MySQL?

  • 2012-09-11 00:30:17
  • d-_-b
  • 115760 View
  • 71 Score
  • 6 Answer
  • Tags:   mysql sql rounding

Sponsored Content