Divide by zero error encountered

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 error encountered

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-02 : 00:55:20

I'm getting such message :(

Here's my statement:

COALESCE (SUM(CASE WHEN Hold_GainPermit = 'Y' THEN 1 ELSE 0 END) * 1.0 / SUM(CASE WHEN Advoc_Hold = 'Yes' THEN 1 ELSE 0 END) * 1.0, 0) AS Hold_GainPermit

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-02 : 01:02:10

I got error that says : Incorrect syntax near '>'

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-07-02 : 01:02:11

it's because this is zero: SUM(CASE WHEN Advoc_Hold = 'Yes' THEN 1 ELSE 0 END)

you could do this:


select
case 
  when SUM(CASE WHEN Advoc_Hold = 'Yes' THEN 1 ELSE 0 END) > 0 
    then SUM(CASE WHEN Hold_GainPermit = 'Y' THEN 1.0 ELSE 0 END) / 
         SUM(CASE WHEN Advoc_Hold = 'Yes' THEN 1.0 ELSE 0 END) 
  else 0
end AS Hold_GainPermit

elsasoft.org

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-02 : 01:02:32

I got error that says : Incorrect syntax near '>'

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-07-02 : 01:05:20

are you using sql server? it compiles fine for me...

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 01:10:24

i dont think error is in posted statement.

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-02 : 01:13:20

yes MS SQL 200

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-02 : 01:21:47

it worked with this:

ISNULL(SUM(CASE WHEN Hold_GainPermit = 'Y' THEN 1 ELSE 0 END) * 1.0 / NULLIF (SUM(CASE WHEN Advoc_Hold = 'Yes' THEN 1 ELSE 0 END)
* 1.0, 0), 0) AS Hold_GainPermit

Is that correct?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 03:43:57

quote:
Originally posted by cutiebo2t

it worked with this:

ISNULL(SUM(CASE WHEN Hold_GainPermit = 'Y' THEN 1 ELSE 0 END) * 1.0 / NULLIF (SUM(CASE WHEN Advoc_Hold = 'Yes' THEN 1 ELSE 0 END)
* 1.0, 0), 0) AS Hold_GainPermit

Is that correct?

how did this solve the error posted??

Incorrect syntax near '>'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources