Why does SQL Server round off results of dividing two integers?
When you do integer division (integer divided by integer) you always get an integer answer. 50/100 = .50, which is 0 in integer-speak.
Have you tried dividing MY_COLUMN by 100.0?
Integer division round off to 2 decimal places in SQL Server
When we divide we can use an integer, which will produce an integer result, or a decimal by adding a decimal point (with or without a zero) which will give a decimal result with the number of decimal places determined by the format, or a floating point by adding an e, which will use the number of significant decimal places without trailing zeros. The following test schema demonstrates the difference between them.
I would seem that ROUND(2e/3,2)
is the most concise way to arrive at 2 decimal places.
SELECT
3e/5 FloatingPoint,
3./5 DecimalDivision,
CONVERT(DECIMAL(3,2), 3./5) DecimalDivision,
Round(3e/5,2) RoundedFloat
FloatingPoint | DecimalDivision | DecimalDivision | RoundedFloat
------------: | --------------: | --------------: | -----------:
0.6 | 0.600000 | 0.60 | 0.6
SELECT
2e/3 FloatingPoint,
2./3 DecimalDivision,
CONVERT(DECIMAL(3,2), 2./3) DecimalDivision,
Round(2e/3,2) RoundedFloat
FloatingPoint | DecimalDivision | DecimalDivision | RoundedFloat
----------------: | --------------: | --------------: | -----------:
0.666666666666667 | 0.666666 | 0.67 | 0.67
db<>fiddle here
How to get a float result by dividing two integer values using T-SQL?
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)
SQL Server, division returns zero
Either declare set1 and set2 as floats instead of integers or cast them to floats as part of the calculation:
SET @weight= CAST(@set1 AS float) / CAST(@set2 AS float);
SQL divide two integers and get a decimal value error
Convert to decimal before the divide, not after. The convert for answer format.
SELECT
CONVERT( DECIMAL(4,3)
, ( CONVERT(DECIMAL(10,3), abc) / CONVERT(DECIMAL(10,3), xyz) )
) AS def
Divide integers and round to 3 decimal places with one cast?
Dividing two int values t-sql has only two possible outcomes:
- error 8134 (division by zero)
- an int value again
It should be sufficient to coerce one of the operands to numeric
:
select round(1.0/3,3,0)
To avoid trailing zeroes, another way:
select cast(1.0/3 as decimal(18,3))
This of course works only with numeric literals. If you have a column or parameter value, a cast
is still needed.
This feeble attempt at code golf shaves off one more character:
select convert(decimal(9,3),1.0/3)
Integer division in sql server
In the first you are getting the result of two integers and then casting the result as DECIMAL(9,2). In the second you're just dividing two integers and that's expected.
If you cast one of the integers as a decimal BEFORE you do the division, you'll get a decimal result.
SELECT 151/CAST(6 AS DECIMAL (9,2))
Rounding UP in SQL Server?
Did you try Casting either the numerator and the denominator as float and then using Cieling?
Integer operations always give integers. Try the following -
SELECT @TotalPages = CEILING((SELECT cast(COUNT(*) as float) FROM #TempItems) / @RecsPerPage )
Division of integers returns 0
You should cast before you divide, but also you were missing a subquery to get the total count from the table. Here's the sample.
select
random_int,
count(random_int) as Count,
cast(count(random_int) as decimal(7,2)) / cast((select count(random_int) from test) as decimal(7,2)) as Percent
from test
group by random_int
order by random_int;
Related Topics
Divide by Zero/Null Workaround in Ssrs 2008 Report
SQL Server Identity Column Values Start at 0 Instead of 1
What Is Causing Foreign Key Mismatch Error
Does 'Select' Always Order by Primary Key
Mysql: "The Select Would Examine More Than Max_Join_Size Rows"
Building a Comma Separated List
Does Union All Guarantee the Order of the Result Set
Aggregating (X,Y) Coordinate Point Clouds in Postgresql
Count Returning Blank Instead of 0
Select from Table with Varying in List in Where Clause
Deferrable Constraints in SQL Server
How to Create and Query Linked Database Servers in SQL Server
Store Multiple Bit Values in a Single Table Column
Get Start and End Date from Week Number SQL Server
Where in Condition Not Accepting String Value
Select Rows Where Column Value Has Changed