Subtracting Two Column With Null

Question

I use the following

 select TotalCredits - TotalDebits as Difference
 from 
 (
 select
 (select sum(TOTALAMOUNT) from journal where memberid=48 and CREDIT =1) as TotalCredits,
 (select SUM(totalamount) from Journal where MEMBERID=48 and DEBIT =1) As TotalDebits
 ) temp

this returns one field with my difference, the problem i am occuring is that if the table has no credit, but has debits, the temp table contains a NULL value in the TotalCredits Field which prohibts math being done. (Vica Versa on has Credits but no Debits) I have tried coalese but cant seem how to make it work.

rationally i need to check if:

sum(TOTALAMOUNT) from journal where memberid=48 and CREDIT =1 as TotalCredits is 
null then totalcredits = 0 and visa versa

sql server 2008

Answer 1

 select ISNULL(TotalCredits,0) - ISNULL(TotalDebits,0) as Difference
 from 
 (
 select
 (select sum(TOTALAMOUNT) from journal where memberid=48 and CREDIT =1) as TotalCredits,
 (select SUM(totalamount) from Journal where MEMBERID=48 and DEBIT =1) As TotalDebits
 ) temp

Answer 2

Change your query to conditional aggregation and it fixes the problem:

select sum(case when credit = 1 then TotalAmount else -TotalAmount end) as Difference
from Journal
where memberid = 48 and (credit = 1 or debit = 1); 

EDIT:

If you have the case where credit and debit could both be 1, then use:

select (sum(case when credit = 1 then TotalAmount else 0 end) -
        sum(case when debit = 1 then TotalAmount else 0 end)
       ) as Difference
from Journal
where memberid = 48 and (credit = 1 or debit = 1);