Dividing 2 numbers in Sql Server

I am doing SQL Server query calculations and the division always gives me zero.

SUM(sl.LINES_ORDERED) 
, SUM(sl.LINES_CONFIRMED)
, SUM(sl.LINES_CONFIRMED) / SUM(sl.LINES_ORDERED) AS 'Percent'

The sample dataset returned is:

In this example, the third row should have a division value of 1.02, but it's shown as zero. Why is that?

Answers


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.


Try it like this:

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

The two problems are that you

  1. perform an integer division (SUM returns an integer), and
  2. you swapped the dividend and the divisor (that's why you get zero instead of one).

This is how you could fix it (note that LINES_ORDERED and LINES_CONFIRMED are swapped):

SUM(sl.LINES_ORDERED) 
, SUM(sl.LINES_CONFIRMED)
, (1.0*SUM(sl.LINES_ORDERED)) / SUM(sl.LINES_CONFIRMED) AS 'Percent'

It will be because you're divinding two integers.

Convert the two values in the division to decimals first:

, SUM(convert(decimal(12,2),sl.LINES_CONFIRMED)) 
/ SUM(convert(decimal(12,2),sl.LINES_ORDERED)) AS 'Percent'

Need Your Help

Using static variables in Spring annotations

java spring spring-security spring-annotations

I'm using spring's PreAuthorize annotation as follows: