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


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, 2011 at 12:08 PM in Default

avatar image

1.4k 100 114 121

(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]

     SUM(ISNULL(ColumnA, 0) 
         + ISNULL(ColumnB, 0))

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

[Code Sample for point 2]

     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, 2011 at 12:26 PM

avatar image

Kevin Feasel
6.2k 4 8 15

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, 2011 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, 2011 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, 2011 at 12:11 PM

avatar image

1.3k 75 104 147

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

answered Mar 24, 2011 at 01:52 PM

avatar image

Håkan Winther
16.6k 37 46 58

(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, 2011 at 11:44 AM

avatar image

1.4k 100 114 121

Where do you do this?

Mar 28, 2011 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, 2011 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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Mar 24, 2011 at 12:08 PM

Seen: 4798 times

Last Updated: Mar 24, 2011 at 12:10 PM

Copyright 2018 Redgate Software. Privacy Policy