Search code examples
sqlsql-serversumdivide

SQL division giving wrong answer

Possible Duplicate:
What is wrong with this SQL Server query division calculation?

I am dividing two values in SQL like 

SELECT SUM(totalCalls), 
    SUM(EnteredBTN), 
    SUM(EnteredBTN) * 100 / SUM(totalCalls),
    SUM(EnteredACC), 
    SUM(EnteredACC) / SUM(totalCalls), 
    SUM(SentToTrans),
    SUM(SentToTrans) * 100 / SUM(totalCalls),
    SUM(Complete_Information), 
    SUM(Complete_Information) * 100 / SUM(SentToTrans)

Now when I do SUM(EnteredACC)/SUM(totalCalls) i.e 7/48, it should give me .14 but it gives 0 instead.

When I do SUM(EnteredACC)*100/SUM(totalCalls) it should give 14.58 (or 15 when rounded off) but it gives 14 as the result. Can anyone help please?

Solution

  • The answer is simple, most likely the data type of the columns EnteredACC and totalCalls is INT (or SMALLINT, BIGINT,etc), therefore it your result is an INT. So, 14.58 becomes 14 and 0.14 becomes 0. You need to do a cast (implicit or explicit) to your datatype:

    SUM(CAST(EnteredACC AS DECIMAL(16,4))/SUM(totalCalls)