Dividing 2 Numbers in SQL Server

Dividing 2 numbers in Sql Server

try

SUM(sl.LINES_CONFIRMED) * 1.0 / SUM(sl.LINES_ORDERED)

An integer devision can only return full numbers and not floating point numbers. You can force a floating point division like in the example above.

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)

Divide two numbers in SQL server

I suspect the type of @opendays

Declare @opendays as decimal(18,8))

Dividing 2 numbers returns 0

I would do it differently, using two sums:

select sum
       ( case
         when endOfUse is null and addDate >= '1/1/2014'
         then 1
         else 0
         end
       )
       * 100.0 -- if you want the usual 0..100 range for percentages
       /
       sum
       ( case
         when addDate >= '1/1/2014'
         then 1
         else 0
         end
       )
       percentage
from   saxref..AuthCycle

How to get percentage in TSQL when dividing 2 integers?

Sql Server sees only integer arguments and returns integer result. Make one of two first arguments numeric / decimal

select a * 100./b

or

select cast(cast(a as decimal(18,4)) * 100/b as decimal(18,2))

this will not help:

select a/b*100.

because of the order of operations first opeation ( division )is still integer.

Test case to play with

declare @a int=170,
        @b int=288;

select  cast(@a as decimal)*100/@b   --59.02777777777
      , @a*100./@b  --59.02777777777
      , cast (@a*100./@b as decimal(18,2)) --59.03

SQL Dividing two SUM() Functions

Your database is probably doing integer division. Simply do the calculation as:

select type, date, SUM(CountWin) as CountWin, SUM(CountFail) as CountFail,
      SUM(CountFail) * 100.0 / NULLIF(SUM(CountWin), 0) as Failure_percent
FROM #my_temp_table
WHERE date > DATEADD(day, -7, getdate())
GROUP BY type, date;

Notes:

• Your where clause is using the time on getdate(). More likely you want: date > dateadd(day, -7, cast(getdate() as date)).
• The NULLIF() prevents division by 0.
• Your calculation is a ration, not a percent. Perhaps you intend: SUM(CountFail) * 100.0 / NULLIF(SUM(CountWin + CountFail))

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

---

Related Topics