« invites aplenty
jalouda hacked to death »

calculated columns in sql server

I frequently suffer from Mondayitis but it can strike on Tuesdays too!

I was creating a stored procedure in SQL Server this morning involving GROUP BY, CASE statements, aggregate functions, ISNULL and COALESCE for good measure. Everything was working fine, except that one of the calculated columns was zero all the way down, even though none of the data was. The same data was used in calculations for other columns and was giving the correct answer so it seemed bizarre to me. I tried removing a function here and a function there to figure out what the hell was going on…

And then *finally* it hit me… I was dividing 2 integer columns! Yes, that rudimentary programming lesson about rounding precision when working with numeric data types was lost on me :D So, once I changed SUM(a)/SUM(b) to CAST(SUM(a) AS FLOAT)/SUM(b) everything was OK. Phew!

I document this nonsense here because I just *know* I’ll do it again someday and maybe I’m not the only silly billy??? What a muppet I am :)

This entry was posted on Tuesday, January 18th, 2005 at 1:30 pm and is filed under databases. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

3 Responses to “calculated columns in sql server”

  1. julio cesar Says:
    July 24th, 2007 at 6:45 pm

    thanks for the post. It helped me work out the same problem. I was deviding two numbers and kept getting zero as a result. It was fixed once I cast one of the operands to a float.

  2. Jan Says:
    January 23rd, 2008 at 2:01 pm

    Thank you, this was very helpfull

  3. Chad Rowley Says:
    April 15th, 2008 at 2:33 pm

    Thank you so much. You helped me solve a problem I worked all day on yesterday. I really appreciate you documenting what you call nonsense. You are a blessing.

Leave a Reply