Divide by zero - 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

General SQL Server Forums

New to SQL Server Programming

Divide by zero

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-07-17 : 13:30:20

How can I handle division by zero errors in this select statement?


select cus_no, sum(tot_sales)/sum(tot_cost) as Margin
from sales

if sum(tot_sales) is null or zero and sum(tot_cost) is null or zero then make my result zero.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-17 : 13:32:02

select cus_no, case when sum(tot_cost) = 0 then 0 else sum(tot_sales)/sum(tot_cost) end as Margin
from sales

and you will need a
group by cus_no

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-17 : 13:32:08

one way - assuming you want a zero for a final answer:
select cus_no, sum(tot_sales)/isNull(nullif(sum(tot_cost),0),0) as Margin
from sales

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-17 : 13:36:18

quote:
Originally posted by TG

one way - assuming you want a zero for a final answer:
select cus_no, sum(tot_sales)/isNull(nullif(sum(tot_cost),0),0) as Margin
from sales

Be One with the Optimizer
TG

Small adjustment:

select cus_no, isNull(sum(tot_sales)/nullif(sum(tot_cost),0),0) as Margin
from sales

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources