#### [SOLVED] How to get a float result by dividing two integer values?

By LaBracca

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?

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

Might be a little late, but realize that `CAST`ing 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

## 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

`CAST`ing might not work in MySQL.

@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.

### [SOLVED] Formatting Decimal places in R

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

### [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
• Tags:   c# division