x

Avoid a Divide by Zero Exception using Select Statement

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

From view

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

Example:

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.
more ▼

asked Mar 24 '11 at 12:08 PM in Default

technette gravatar image

technette
1.1k 77 91 101

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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]

select 
    SUM(ISNULL(ColumnA, 0) 
        + ISNULL(ColumnB, 0))
from
    SomeTableOrView

2 - For the actual division process, do a case statement.

[Code Sample for point 2]

case
    when Dividend = 0 then 0
    else Divisor / Dividend
end as Quotient
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.
more ▼

answered Mar 24 '11 at 12:26 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

Thank you Kevin. I'll try this and get back to you. So this should work even if the data is stored in the view as 0 where there is no value?
Mar 24 '11 at 12:39 PM technette
If 0 means the value 0 and "no value" in your view, then the case statement will return 0 in both circumstances. If there is a business significance to needing to display something different for "there were no records found" than for "the sum of the values which make up the dividend is zero," it might be good to include a count of the number of records and display something in your report if that returns zero.
Mar 24 '11 at 12:46 PM Kevin Feasel
(comments are locked)
10|1200 characters needed characters left
I usually use a ISNULL(ColumnA, 1) to avoid the divide by zero error.
more ▼

answered Mar 24 '11 at 12:11 PM

Slick84 gravatar image

Slick84
1.3k 75 102 142

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Mar 24 '11 at 01:52 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(comments are locked)
10|1200 characters needed characters left

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))
more ▼

answered Mar 28 '11 at 11:44 AM

technette gravatar image

technette
1.1k 77 91 101

Where do you do this?
Mar 28 '11 at 11:48 AM Magnus Ahlkvist
In SQL Reports, you can create calculated fields on the datasource either in code ASP.NET or in the properties of the datatsource.
Mar 28 '11 at 11:54 AM technette
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x103

asked: Mar 24 '11 at 12:08 PM

Seen: 3325 times

Last Updated: Mar 24 '11 at 12:10 PM