CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
user3633843
user3633843

Reputation: 1

Computed column not working in SQL Server

I am having a problem with a computed column in a SQL Server 2008 R2 table.

I have the following equation 

round((([m1]+[m2]+[m3])/3),0)

in the computed column, and the problem is if you have m1=99, m2=100 and m3=100, the result in the computed column should be 100 but what I am getting is 99

Thank you

Upvotes: 0

Views: 691

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48786

You just need to force SQL Server to use decimal computations instead of assuming all to be INTs. You can do this either by casting (as others have noted) or you can just give it a decimal value for one of the operands. And since you have a hard-coded 3, you can use that one to force to decimal by adding .0, as in:

round((([m1]+[m2]+[m3])/3.0),0)

Upvotes: 0

Rahul
Rahul

Reputation: 77866

You will have to convert it to numeric type like

ROUND((convert(numeric,[m1]+[m2]+[m3])/3),0)

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

This is because you get an integer division *, which truncates the result before it gets to ROUND. Evaluate your expression step-by-step to see what's going on:

  • [m1]+[m2]+[m3] is computed, giving you 299
  • The result of [m1]+[m2]+[m3] is integer-divided by 3, giving you 99
  • ROUND(99, 0) gets called, producing 99.

Here is how you can fix it:

ROUND((cast([m1]+[m2]+[m3] as decimal)/3),0)

Here is a running demo on sqlfiddle, which returns 100.

* I assume that all three of your columns are integer, which would be consistent with the behavior that you observe.

Upvotes: 1

Related Questions