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?
This question is related to
sql-server
sql-server-2005
You will need to cast or convert the values to decimal before division. Take a look at this http://msdn.microsoft.com/en-us/library/aa226054.aspx
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
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)
SELECT CAST (col1 as float) / col2 FROM tbl1
One cast should work. ("Less is more.")
From Books Online:
Returns the data type of the argument with the higher precedence. For more information about data type precedence, see Data Type Precedence (Transact-SQL).
If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated
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.
just convert denominator to decimal before division e.g
select col1 / CONVERT(decimal(4,2), col2) from tbl1
CAST( ROUND(columnA *1.00 / columnB, 2) AS FLOAT)
Source: Stackoverflow.com