How to get a float result by dividing two integer values using T-SQL?

Question

Using T-SQL and Microsoft SQL Server 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?

score 390 · Accepted Answer · answered Jul 30 '12 at 12:05

390

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)

answered Jul 30 '12 at 12:05

Richard

29,854
11
77
120

2

Or you can cast them as decimals, if you want a decimal result. – Tim Mar 20 '14 at 14:05
59

SELECT 1.0*MyInt1/MyInt2 – Troglo Feb 11 '15 at 15:33
@TroubleZero for MySQL use `decimal` – itsazzad Feb 09 '16 at 05:38
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. – Yasin Yörük Feb 11 '16 at 11:38
4

no need to cast both dividend and divisor, see @M.S.' answer http://stackoverflow.com/a/30639343/2866644 – robotik Jul 19 '16 at 14:02
Anyone who is looking for help for DB2, this will work. For whatever reason, DECIMAL() does not work properly. – SovietFrontier Dec 05 '18 at 20:49
And if you're trying to store it into a variable or cell, make sure your variable/cell is a float data-type – Mayer Spitz Jan 27 '20 at 16:39

score 89 · Answer 2 · answered Jul 30 '12 at 09:55

89

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

answered Jul 30 '12 at 09:55

xiaowl

5,177
3
27
28

2

You can even leave out the zeroes. – John Mar 06 '17 at 11:34

score 58 · Answer 3 · edited Jul 19 '16 at 15:19

58

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)

edited Jul 19 '16 at 15:19

robotik

1,837
1
20
26

answered Jun 04 '15 at 08:55

M.S.

4,283
1
19
42

score 33 · Answer 4 · answered Jul 30 '12 at 09:55

33

use

select 1/3.0

This will do the job.

answered Jul 30 '12 at 09:55

stb

3,405
2
17
24

Supreme Dolphin · Answer 5 · 2019-11-28T13:17:15.517

18

I understand 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 one. 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.

edited Nov 28 '19 at 13:17

answered Jan 24 '16 at 09:41

Supreme Dolphin

2,248
1
14
23

5

MySQL does allow `CAST`, it just doesn't allow casting to `FLOAT`. Cast to `DECIMAL` instead. See e.g. https://stackoverflow.com/questions/7368163/how-can-i-convert-a-string-to-a-float-in-mysql. – markusk Sep 04 '17 at 18:45

score 9 · Answer 6 · answered Feb 11 '15 at 15:32

9

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

answered Feb 11 '15 at 15:32

Troglo

1,497
17
18

2

I liked this the most, it even looks nice for percentage calculations: `SELECT 100.0 * @Elapsed / @Total` – EliSherer Oct 17 '18 at 17:49

score 3 · Answer 7 · answered Feb 13 '17 at 04:22

3

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.

answered Feb 13 '17 at 04:22

nazmul.3026

918
1
9
20

score 3 · Answer 8 · answered Mar 08 '17 at 10:33

3

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

answered Mar 08 '17 at 10:33

trojan

1,465
19
27

score 1 · Answer 9 · answered Aug 30 '22 at 01:56

1

I was surprised to see select 0.7/0.9 returning 0.8 in Teradata given they're already as floats/decimal numbers! I had to do cast(0.7 as float) to get the output that I was after.

answered Aug 30 '22 at 01:56

samsamara

4,630
7
36
66

score 0 · Answer 10 · answered Oct 27 '22 at 07:16

When using literals, the best way is to "tell" SQL which type you mean.

if you want a decimal result, add decimal point ".0" to your numbers:

SELECT 1.0 / 3.0

Result
0.333333

if you want a float (real) result, add "e0" to your numbers:

SELECT 1e0 / 3e0

Result
0.333333333333333

How to get a float result by dividing two integer values using T-SQL?

10 Answers10

Use this

Linked

Related