Divide in SQL Server

Question

In SQL Server 2005 Express, the result is below

SELECT 100 / 15 --Result 6

But I wanna get approximate value 7 (like using calculator)

100 / 15 = 6.6666..

How to make it in SQL Server?

Now I got it. Thanks all :). SELECT ROUND(100 / 15.00,0) – soclose Mar 01 '11 at 06:05 — soclose, Mar 01 '11 at 06:05

score 15 · Accepted Answer · answered Mar 01 '11 at 05:59

15

You have to use decimal numbers e.g. 100 / 15.0. If you use integers the result is interpreted as an integer and truncated to the largest integer that is smaller than or equal to the result.

answered Mar 01 '11 at 05:59

Aleksi Yrttiaho

8,266
29
36

score 8 · Answer 2 · answered May 11 '13 at 15:12

8

SELECT cast(100 as float) / (15 as float)

answered May 11 '13 at 15:12

user2373127

81
1
1

score 5 · Answer 3 · answered Mar 01 '11 at 05:59

You want SQL Server to perform floating-point divison, as opposed to integer division. If you're using literal values, as in your example, suffix the denominator with .0 to force SQL Server to treat it as a floating-point value.

SELECT 100 / 15.0

Otherwise, make sure you declare your variables as FLOAT.

score 2 · Answer 4 · answered Mar 01 '11 at 05:59

2

Try declaring variables as floats:

DECLARE @var1 FLOAT
DECLARE @var2 FLOAT
SET @var1 = 100
SET @var2 = 15
SELECT @var1 / @var2

answered Mar 01 '11 at 05:59

Dubmun

478
2
10

Divide in SQL Server

4 Answers4

Linked