Decimal values in SQL for dividing results

Question

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

User · Accepted Answer

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

User · Answer

just convert denominator to decimal before division e g  select  col1   CONVERT decimal 4 2   col2   from  tbl1

User · Answer

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

User · Answer

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

User · Answer

CAST  ROUND columnA  1 00   columnB  2  AS FLOAT

User · Answer

SELECT CAST  col1 as float    col2 FROM tbl1 One cast should work    quot Less is more  quot   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

[sql-server] Decimal values in SQL for dividing results

Examples related to sql-server

Examples related to sql-server-2005