CODEWITHSUNDEEP
sqlsql-server
Frederica
Frederica

Reputation: 35

Calculator vs SQL Server giving different results

I have a simple calculation sum(eom_count)/ 3. It results in a value of 82, rather than 84.

When I use a calculator, I get 84:

select sum(eom_mbr_count ) from elig table = 252
select sum(eom_mbr_count/3) from elig table = 82

Calculator on computer 252/3 = 84, I know 84 is correct, your help is appreciated. Thanks

Upvotes: 1

Views: 218

Answers (2)

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391396

You were unlucky in that the result of your sum was a number wholly divisible by 3, otherwise you might've spotted the problem yourself and at least wondered why that is.

As is now evident by my comments and your reply on your question, your field is an integer field, and when you divide one integer by another integer you get "integer division" which does not yield a floating point number, it yields another integer.

As an example, if you divide 1/3, most programming languages and database engines with a hard separation between integers and floating point numbers will give you a result of 0, not 0.333333333333..... If an integer division will yield another integer, it cannot yield .333333333333, it will always yield a whole integer, which in this case will be 0.

So what happened in your case is that you have 1 or more rows which, when your eom_mbr_count field is divided by 3, using integer division, loses the fractional part, and thus produces a result that looks wrong.

As an example of this, consider having 9 rows in your database with the value 1 in the eom_mbr_count field for all those rows.

If you sum the field for all the rows, you get 9. However, if you sum the eom_mbr_count/3 result for all those rows you will get 0, because each individual row result will produce 0.

The simplest way to fix this is to postpone the division until you've summed all the rows:

select sum(eom_mbr_count)/3 from elig table = 82

Notice that I moved /3 out of the parenthesis there, which means will divide the sum by 3, not sum each element divided by 3.

Another solution might be to divide by 3.0, which will produce a floating point division, but again I would advise to use the above approach, to avoid propagating floating point errors for each rows.

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269923

SQL Server does integer division, so 1/2 is 0 rather than 0.5. Just divide by 3.0 instead of 3:

select sum(eom_mbr_count / 3.0) /
from elig

Upvotes: 1

Related Questions