Decimal values in SQL for dividing results

Sql ServerSql Server-2005

Sql Server Problem Overview

In SQL, I have col1 and col2. Both are integers.

I want to do like:

select col1/col2 from tbl1

I get the result 1 where col1=3 and col2=2

The result I want is 1.1

I put round(col1/col2,2). The result is still 1.

I put decimal(col1/col2,2). The decimal is not built in function.

How can I do exactly to get 1.1?

Sql Server Solutions

Solution 1 - Sql Server

Just another approach:

SELECT col1 * 1.0 / col2 FROM tbl1

Multiplying by 1.0 turns an integer into a float numeric(13,1) and so works like a typecast, but most probably it is slower than that.

A slightly shorter variation suggested by Aleksandr Fedorenko in a comment:

SELECT col1 * 1. / col2 FROM tbl1

The effect would be basically the same. The only difference is that the multiplication result in this case would be numeric(12,0).

Principal advantage: less wordy than other approaches.

Solution 2 - Sql Server

You will need to cast or convert the values to decimal before division. Take a look at this

For example

DECLARE @num1 int = 3 DECLARE @num2 int = 2

SELECT @num1/@num2

SELECT @num1/CONVERT(decimal(4,2), @num2)

The first SELECT will result in what you're seeing while the second SELECT will have the correct answer 1.500000

Solution 3 - Sql Server

SELECT CAST (col1 as float) / col2 FROM tbl1

One cast should work. ("Less is more.")

From [Books Online][1]:

> Returns the data type of the argument with the higher precedence. For more information about data type precedence, see [Data Type Precedence (Transact-SQL)][2]. > > If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated

[1]: "/ (Divide) (Transact-SQL)" [2]:

Solution 4 - Sql Server

CAST( ROUND(columnA *1.00 / columnB, 2) AS FLOAT)

Solution 5 - Sql Server

There may be other ways to get your desired result.

Declare @a int
Declare @b int
SET @a = 3
SET @b=2
SELECT cast((cast(@a as float)/ cast(@b as float)) as float)

Solution 6 - Sql Server

just convert denominator to decimal before division e.g

select col1 / CONVERT(decimal(4,2), col2) from tbl1


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionwilliamView Question on Stackoverflow
Solution 1 - Sql ServerAndriy MView Answer on Stackoverflow
Solution 2 - Sql ServerShiv KumarView Answer on Stackoverflow
Solution 3 - Sql ServerbensiuView Answer on Stackoverflow
Solution 4 - Sql ServerDonovan Paul FroonView Answer on Stackoverflow
Solution 5 - Sql Serverashish.chotaliaView Answer on Stackoverflow
Solution 6 - Sql ServerAsad GulzarView Answer on Stackoverflow