SQL division giving wrong answer

Question

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?

You're dividing an integer by an integer. This results in an integer. If you want a decimal result, try `*100.0` or `*1.0`. If you want to specify number of decimal places, use an explicit cast like Lamak's and Void Ray's answers - but please specify an explicit precision + scale. — Aaron Bertrand, Oct 31 '12 at 17:22

score 7 · Accepted Answer · answered Oct 31 '12 at 17:24

7

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)

answered Oct 31 '12 at 17:24

Lamak

69,480
12
108
116

Thanks i had everything set to decimal type except for this particular temporary table . I missed it somehow thanks for the help . – Oct 31 '12 at 18:51

Void Ray · Answer 2 · 2012-10-31T17:52:59.807

0

Try: select cast(cast(7 as decimal(18,2))/cast(48 as decimal(18,2)) as decimal(18,2))

edited Oct 31 '12 at 17:52

answered Oct 31 '12 at 17:22

Void Ray

9,849
4
33
53

4

If you're going to explicitly cast as decimal, you should include precision and scale. Do you know what the defaults are? Does the OP? Will future readers? – Aaron Bertrand Oct 31 '12 at 17:23
1

I was just trying to point out that the issue is with the data type. Precision and scale are not in question here. – Void Ray Oct 31 '12 at 17:31
It is still poor practice to ever - especially when demonstrating code - exclude precision/scale/length. Here are my arguments about VARCHAR but they apply equally to DECIMAL, VARBINARY, DATETIME2, etc.: https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx – Aaron Bertrand Oct 31 '12 at 17:42
Aaron, I would have to agree re: Varchar, but for decimal and especially for some demo code, defaults are just fine; IMHO. – Void Ray Oct 31 '12 at 17:52
you're missing my point. And that's okay, hopefully other readers will get it. – Aaron Bertrand Oct 31 '12 at 17:53
well, I can say the same; let's leave it at that... – Void Ray Oct 31 '12 at 17:55

score 0 · Answer 3 · answered Oct 31 '12 at 17:23

0

You need floating point division, not integer division.

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

answered Oct 31 '12 at 17:23

PRNDL Development Studios

3,270
2
35
50

3

I disagree that FLOAT is the right answer here. – Aaron Bertrand Oct 31 '12 at 17:24
Out of curiosity, why is `decimal` more appropriate? – PRNDL Development Studios Oct 31 '12 at 17:25
Because the OP only seems to want two decimal places. Why would FLOAT be better, aside from the fact that you will end up with much bigger numbers to look at such as `14.58000000000000000001`? – Aaron Bertrand Oct 31 '12 at 17:40
Ahh gotcha. So it is not a matter of performance, but a matter of style? I only have about a year of SQL experience, so I didn't know. Thanks. – PRNDL Development Studios Oct 31 '12 at 19:59
Well, and accuracy, too. You should only use FLOAT, IMHO, if you specifically want its approximation properties. In *most* business applications (I would never say *all*), FLOAT is not warranted. I've been using SQL Server since 6.5 and I have yet to deal directly with any customer system where FLOAT was the better choice. I'm sure they exist, but when we're talking about dividing integers and expressing percentages, FLOAT is not the right choice. Again, IMHO. – Aaron Bertrand Oct 31 '12 at 20:02

SQL division giving wrong answer

3 Answers3

Linked

Related