SQL Server (Avoiding Dividing by a null value)

Question

I have the following query:

UPDATE FinanceTable
SET    PercentageComplete = (SELECT ( Sum(LabourFinance.HoursTaken / LabourFinance.Hours) / Count(LabourID) )
                             FROM   LabourFinance
                             WHERE  FinanceID = (SELECT TOP 1 FinanceID
                                                 FROM   FinanceTable
                                                 ORDER  BY changedate DESC)
                             GROUP  BY FinanceID)
WHERE  FinanceID = (SELECT TOP 1 FinanceID
                    FROM   FinanceTable 
                    ORDER  BY changedate DESC)

what this query does is select 1 specific cell based upon a changedate, and i am using that to update a cost value in my FinanceTable.

this scenario is causing me the issue:

I have allocated 100 hours to complete a task
HoursTaken is currently 0 after inserting into the table as ive not started yet
when calculating PercntageComplete, SQL Server will not let me do (0 / 100), therefore causing an error...

How can i prevent this? my desired outcome would be PercentageComplete = 0%

thanks for any input :) below is the error message i get by runnig the query above

enter image description here

`0/100` should be absolutely fine, and return `0`. You get problems with `0` when it the divisor, not the dividend. — Damien_The_Unbeliever, Oct 09 '14 at 13:03
Possible problems are Either you have allocated '0' hours for task or the count of labors for the condition is'0'.Apart from this i see no issue — Recursive, Oct 09 '14 at 13:06
It would also help (not just us but you and anyone else reading your SQL) if you formatted it in a nice way. I would suggest sticking to all upper or lower case for SQL keywords for example, and use indentation. — DavidG, Oct 09 '14 at 13:08
@crezzer7 - Whether your query is correct. i see error in your query — Pரதீப், Oct 09 '14 at 13:11

score 2 · Accepted Answer · edited May 23 '17 at 12:20

2

Refer This

How to avoid the “divide by zero” error in SQL?

UPDATE FinanceTable
SET    PercentageComplete = (SELECT COALESCE (Sum(COALESCE (LabourFinance.HoursTaken / NULLIF(LabourFinance.Hours, 0), 0)) / NULLIF(Count(LabourID), 0), 0)
                             FROM   LabourFinance
                             WHERE  FinanceID = (SELECT TOP 1 FinanceID
                                                 FROM   FinanceTable
                                                 ORDER  BY changedate DESC)
                             GROUP  BY FinanceID)
WHERE  FinanceID = (SELECT TOP 1 FinanceID
                    FROM   FinanceTable
                    ORDER  BY changedate DESC)

edited May 23 '17 at 12:20

Community

1
1

answered Oct 09 '14 at 13:09

Recursive

954
7
12

how do i apply that to my scenario though, ive seen this already and failed trying to get it to work – Crezzer7 Oct 09 '14 at 13:12
updated with your query.Try it – Recursive Oct 09 '14 at 13:27
I tried using COALESCE earlier, what is it doing? and thankyou for the help – Crezzer7 Oct 09 '14 at 13:28
1

COALESCE returns the current value of the first expression that initially does not evaluate to NULL.http://msdn.microsoft.com/en-IN/library/ms190349.aspx – Recursive Oct 09 '14 at 13:31
cheers pal, this would have taken me ages to work out... – Crezzer7 Oct 09 '14 at 13:33

SQL Server (Avoiding Dividing by a null value)

1 Answers1