September 26, 2011 at 1:34 pm
I am trying to add a column to a simple select list that is derived from arithmetic on two other columns. It is a simple division and multiplication expression but since the first part of the expression results in a negative number my result is always 0 (zero). I'll post my whole sql statement then I will post a dummied down version that is essentially the same.
SELECTDateTime,SiteID,Attendance,Freecount,Reducedcount,Paidcount,Enrollment,
(freecount/enrollment)*freecount as freecount2
FROMOpSumm
dummied down - Select (100/200)*100
The output is always 0(zero)
I have tried convert and cast but with no success.
Any ideas?
Thanks
Dan Tuma
September 26, 2011 at 1:44 pm
It's doing integer division. 100/200 = 0.
The simplest way to not use integer division is to multiply by a decimal or float value (such as 1.00) first.
1.00 * 100/200 = 0.500000
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 26, 2011 at 1:52 pm
It might be easier to just cast one of your values in your division inline like this.
select (CAST(freecount as decimal) / enrollment) * freecount from OpSumm
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2011 at 2:32 pm
Sean,
Never thought of Cast(ing) just one piece of the pie. This works perfectly. Thank you for your help
And thanks to those who also replied.
Dan
September 26, 2011 at 2:39 pm
You're welcome. glad that works and thanks for letting us know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply