how to replace the result of sum by 0 if it is Null

Question

I have 3 tables:

• Projects ( a project can have many phases)
• Phases (a phase can have 0 or many tasks)
• Tasks (primary key: id_task, id_phase, id_project)

Every task has cost and the cost of a phase is the SUM(cost) of its tasks.

I want to update the cost of every phase in the project, I tried this query:

UPDATE Phases SET cost = (SELECT sum(cost) FROM Tasks WHERE Tasks.IDprojet = 'xxx' AND Tasks.RefPhase = Phases.RefPhase)

... but this give me some right values and others are NULL. I used SUM(COALESCE(cost,0)) but nothing changed, maybe the query is wrong or I have to make function to test the value returned by the Select if it is null or not to be changed to 0?

Could you help me please!

Answer 1

You need to use COALESCE on the outside:

UPDATE Phases
SET cost = COALESCE((
    SELECT SUM(cost)
    FROM Tasks
    WHERE Tasks.IDprojet = 'xxx' AND Tasks.RefPhase = Phases.RefPhase
), 0)

The SUM function will return NULL if all values were NULL. The sub-query will return NULL if no rows matched. This should take care of both cases.