division by zero problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

division by zero problem

Author

Topic

s
Starting Member

5 Posts

Posted - 2003-03-03 : 04:08:15

Hi all,

The follwoing giving me divided by zero error
so let me know the correct syntax with out that
erro..

i think that is when i caliculate percentage
it is giving me error...
like if amount for that month in 2002 and 2001
is same...than it is errror..
so let meknow how to avoid that...

select sum(case
WHEN a.oper_year = 2002 THEN a.amount
end) as year2002,
sum(case
WHEN a.oper_year = 2001 THEN a.amount
end) as year2001,

sum(case
WHEN a.oper_year = 2002 THEN a.amount
end) -
sum(case
WHEN a.oper_year = 2001 THEN a.amount
end) as ImprsImprov,

(sum(case
WHEN a.oper_year = 2002 THEN a.amount
end) / (sum(case
WHEN a.oper_year = 2001 THEN a.amount
end) -
sum(case
WHEN a.oper_year = 2002 THEN a.amount
end)))* 100 as Percentage,

b.district_type, b.region_name, b.sun_site,a.map_code
end if

from oper_sundata a, oper_type_new b
where a.site_id = b.sun_site
and b.district_type in (88010)
and b.sun_site in (05001)
and a.map_code in ('e014','eo17')
and a.oper_month = '11'
group by a.map_code,b.district_type, b.region_name, b.sun_site

can any one check and let me knowthe correct code..

thanks

s

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-03 : 07:42:58

Divide by zero means you are trying to divide by zero ... not really a syntax-type error but a mathematical one. You can never divide any number by zero.

What you need to do is replace your statements that divide with this basic syntax, using the CASE statement:

CASE WHEN B <> 0 THEN A/B ELSE Null END

In that example, you want to divide A by B; the case statement makes sure that B is not zero, and if it is, it returns Null.

- Jeff

M.E.
Aged Yak Warrior

539 Posts

Posted - 2003-03-03 : 12:00:47

Might not work/mean anything for you, but SQL server uses 2 options to define it's behavior during a 0. Try looking them up in BOL
Arithabort and Arithignore

-----------------------
SQL isn't just a hobby, It's an addiction

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-03 : 13:21:11

Your problem seems to be of a logical one, more so than even an arithmetic one.

The part of your select clause:

(sum(case
WHEN a.oper_year = 2002 THEN a.amount
end) / (sum(case
WHEN a.oper_year = 2001 THEN a.amount
end) -
sum(case
WHEN a.oper_year = 2002 THEN a.amount
end)))* 100 as Percentage

doesn't make any sense. If a.oper_year has to be one value. It's can't be both. So that the result of one or the other will always be Null.

I imagine dividing by Null is the same as dividing by zero (which is what you will never have in this example).

What are you trying to solve?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources