[thelist] Avoiding Divide by Zero within SQL...
Anthony Baratta
Anthony at Baratta.com
Fri Nov 30 12:53:26 CST 2001
(rudy is going to fall outta his chair laughing at this, but I swear it's a
different problem with Null. ;-)
I'm trying to run some stats/percentages on some data in MS SQL.
Unfortunately some of the data is "Null".
When I run the following statement:
SELECT
Cast(Cast(((tempEnrollment.Full_Time_Women_Enrolled +
tempEnrollment.Part_Time_Women_Enrolled) /
(tempEnrollment.Full_Time_Men_Enrolled +
tempEnrollment.Part_Time_Men_Enrolled +
tempEnrollment.Full_Time_Women_Enrolled +
tempEnrollment.Part_Time_Women_Enrolled) * 100) As Int) As NvarChar) + "%"
FROM tempEnrollment
The fields that are "Null" turn the whole answer to "Null". If I convert
the Nulls to Zeros during the creation of the tempEnrollment table, I get a
divide by zero error since some times columns are all Null.
So I need to get a result if the denominator is greater than zero;
otherwise I just want 0%.
If this make no sense - let me know.
----
Anthony Baratta
President
Keyboard Jockeys
"Conformity is the refuge of the unimaginative."
More information about the thelist
mailing list