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 😀 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 🙂

5 thoughts on “calculated columns in sql server”

  1. 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. 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.

  3. I tend to multiply one of the columns by 1.0 to keep away from integer division errors. When I need to avoid rounding errors, I convert to a REAL which is comparable to a DECIMAL(18,7) or DECIMAL(18,8). I can’t remember exactly which one. Also, I’m not sure how that compares to a FLOAT as I never use FLOATS.

  4. I love you – such a simple explanation i was being a complete dumbass and this helped me realise i was being so dumb after 10 seconds rather than 2 hours of tearing my hair out

Comments are closed.