[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:

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
Keyboard Jockeys

"Conformity is the refuge of the unimaginative."

More information about the thelist mailing list