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

SQL Server 2005 Forums

Transact-SQL (2005)

Divide by zero

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-14 : 23:27:41

Hi, i know that following syntax can handle divide by zero by changing to NULL, can i know is there any way to get 0 instead of NULL
PRINT NULLIF(0,0)/NULLIF(0,0)

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 23:29:25

[code]ISNULL(NULLIF(0,0)/NULLIF(0,0))[/code]

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-14 : 23:32:06

>.<"" just add in isnull? sifu...got other more efficient method ar? cause i scare confusing
SUM(((total_rm-cost_mr)/total_rm)*100) as SALES
will become
SUM(ISNULL((NULLIF(total_rm-cost_mr, 0)/NULLIF(total_rm, 0)), 0)*100) as SALES,

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 23:37:07

don't think it will make much diff between this a the ISNULL()


SUM
(
    CASE WHEN total_rm <> 0 
         THEN ((total_rm - cost_mr) / total_rm) * 100
         ELSE 0
         END
)  as SALES

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-14 : 23:49:58

hehe thx my sifu ^^

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources