How to return zero instead of null from a subquery when a condition is not met

I have the following data,

Id Value Tag
1   200   C
1   300   D
1   100   D
2   300   D
2   100   D

I want to sum up values of tags and deduct sum of D’s from sum of C’s. The below works fine if both tags are present for an id.

SELECT
    D.sumOfD - C.sumOfC as value,
FROM (
    SELECT IFNULL(ROUND(SUM(value),2),0) as 'sumOfD',Id
    FROM Reports exp WHERE exp.tag = 'D' 
) as D
JOIN (
    SELECT IFNULL(ROUND(SUM(value),2),0) as 'sumOfC' ,Id
    FROM Reports exp WHERE exp.tag = 'C'
) as  C ON C.Id = D.Id
;

But if a tag is not available for an id either one of the subqueries will not return a value. In the above sample data there is only tag D for id 2 so the on C.Id = D.Id condition is failing. In this case i want zero to be returned.

Expected result

Id  Value
1   200 (300+100-200) 
2   400 (300+100-0) -- where 0 is the sum of values for tag C for id 2 which is zero

How can i do this? I am using mysql 8.0.17

Answer

Try the below – using conditional aggregation

select id, 
sum(case when Tag='D' then value else 0 end)-sum(case when Tag='C' then value else 0 end) as diff
from tablename
group by id

Advertisement

Answer