how to set the result of query to zero if its condition becomes false

Question

I have a sql query which returns total of a table from three columns and this is my query:

SELECT c.*,  
   (select sum(t.incoming) - sum(t.outgoing) from transactions t where t.client_id = c.id and currency = 'Dollar') +  
   (select sum(t.equal_to_dollar) from transactions t where t.incoming != 0 and currency != 'Dollar' and t.client_id = c.id) -  
   (select sum(t.equal_to_dollar) from transactions t where t.outgoing != 0 and currency != 'Dollar' and t.client_id = c.id)  
as total from clients c  

my problem is when one of conditions in second and third (inner selects) doesn't meet it will return null value and cause the whole select return null. because null value can't sum or subtract with any value.
I want to know is there any way to set the result of query which its condition evaluate to false to zero so it wouldn't effect on whole query and query will return value of those parts that meet the condition in where clause.
and I don't know is it a good way of calculating of value when the result is important and it should be so accurate?
and I want to thank you in advance for your help :)

Answer 1

You can just use coalesce():

select c.*,  
       ((select coalesce(sum(t.incoming), 0) - coalesce(sum(t.outgoing), 0) from transactions t where t.client_id = c.id and currency = 'Dollar') +  
        (select coalesce(sum(t.equal_to_dollar), 0) from transactions t where t.incoming <> 0 and currency <> 'Dollar' and t.client_id = c.id) -  
        (select coalesce(sum(t.equal_to_dollar), 0) from transactions t where t.outgoing <> 0 and currency <> 'Dollar' and t.client_id = c.id)  
       ) as total
from clients c  ;

Each subquery is an aggregation without a group by, so each returns one row -- which, if there is no match, will have a value of NULL. COALESCE() converts it to 0.

There is no reason for three subqueries, however. Just use a single subquery and do the calculations using conditional aggregation:

select c.*,  
       (select sum(case when currency = 'Dollar'
                        then coalesce(t.incoming, 0) - coalesce(t.outgoing, 0)
                        when t.incoming <> 0
                        then coalesce(t.equal_to_dollar, 0)
                        when t.outgoing <> 0
                        then - coalesce(t.equal_to_dollar, 0)
                        else 0
                   end)
         from transactions t
         where t.client_id = c.id
       ) as total
from clients c  ;