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.
Author |
Topic |
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-06-26 : 02:14:22
|
I tried coalesce but it's not working. I'm getting zero encountered error on this statement. Both numerator and denominor equal zero. I need the results to be zero.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 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-26 : 02:22:14
|
Where you tried coalesce? your posted code doesnot contain coalesce? also it would be better if you give a brief idea of what you're trying to do here. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-06-26 : 02:46:41
|
maybe that's the point. coalesce doesn't work right when you don't use it. elsasoft.org |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-06-26 : 03:11:28
|
And "getting zero encountered error" is most likely "divide by zero" which is a mathematical impossibility. If SUM(CASE WHEN Advoc_Hold = 'Yes' THEN 1 ELSE 0 END) sums up to 0 then you will get this error.- Lumbago |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-06-26 : 03:15:13
|
declare @Hold_GainPermit char(1)declare @Advoc_Hold char(3)select @Hold_GainPermit = 'N'select @Advoc_Hold = 'No'selectSUM(CASE WHEN @Hold_GainPermit = 'Y' THEN 1 ELSE 0 END) * 1.0 / CASE WHEN (SUM(CASE WHEN @Advoc_Hold = 'Yes' THEN 1 ELSE 0 END) * 1.0) = 0 THEN 1.0 ELSE SUM(CASE WHEN @Advoc_Hold = 'Yes' THEN 1 ELSE 0 END) * 1.0ENDGreetingsWebfred |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-26 : 03:26:47
|
or:-selectISNULL(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) |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-06-26 : 03:34:42
|
that looks fine!I have never seen/used NULLIF before...;o) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-26 : 03:37:52
|
quote: Originally posted by webfred that looks fine!I have never seen/used NULLIF before...;o)
it just puts NULL whenever it encounters 0 in denominator so that result becomes NULL. then we can use ISNULL or CAOLESCE to change it value we want (i just gave 0 as default value) |
|
|
|
|
|