I have a huge view from which I will be selecting the sum of individual columns, (putting the sum in a report field) then dividing the sum by another field (result will display on report).
How do I correctly include a statement that will eliminate the divide by zero exception? The columns are decimal
Select sum(column1, column2, column3, column4) as SummarizedName, Column5
where parameter1 = @parameter1 and parameter2 = @parameter2 and parameter3 = @parameter3
I don't know how to include a statement that does the division eliminating the error and producing a new value:
This may be a poor start...
isnull( isnull(SummarizedName,0) / ( nullif(Column5,0)) [NewValuefromDividing]I will eventually turn this into a stored procedure but I need to produce the datasets for now to show the data running through the report correctly.
Here's how I tend to handle it:
1 - For summations, include an ISNULL(ColumnA, 0) or COALESCE(ColumnA, 0) statement around each column you are adding. This will prevent problems like what you would get with a "select 3 + NULL" query (or a grouping which has some null values where you were expecting numbers) returning NULL instead of 3.
[Code Sample for point 1]
2 - For the actual division process, do a case statement.
[Code Sample for point 2]
That way, you won't have null values and won't have a divide by zero problem. You could do an extra when clause in your case statement to handle when Dividend IS NULL, but personally, I'd rather see the NULL come back to let you know that there's a real problem and something got through your error-checking earlier in the process.
I usually use a ISNULL(ColumnA, 1) to avoid the divide by zero error.
answered Mar 24, 2011 at 12:11 PM
You can read more answers on this question:http://ask.sqlservercentral.com/questions/21306/best-way-to-avoid-divide-by-zero
answered Mar 24, 2011 at 01:52 PM
I was able to use the following: I got this answer form Telerik
= Field.A / Field.B= Iif(Field.B = 0, 0, Field.A / Iif(Field.B = 0, 1, Field.B))
answered Mar 28, 2011 at 11:44 AM