CODEWITHSUNDEEP
sqlsql-server
Justin Samuel
Justin Samuel

Reputation: 1083

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:

enter image description here

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

Upvotes: 8

Views: 65809

Answers (4)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727047

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'

Upvotes: 2

Jon Egerton
Jon Egerton

Reputation: 41579

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'

Upvotes: 1

aF.
aF.

Reputation: 66747

Try it like this:

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

Upvotes: 2

juergen d
juergen d

Reputation: 204904

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.

Upvotes: 31

Related Questions