Subtracting two columns using 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 a single field with my difference, the problem is that if the table has no credit but has debit, the temp table contains a NULL value in the TotalCredits field, which prohibits the execution of mathematics. (Vica Versa on has Credits, but no flow rates) I tried coal, but I can't seem 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 conditional aggregation request and fix 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 a case when the amount of the loan and debit can be equal to 1, 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); 

Answer 3

, ,

select COALESCE(TotalCredits,0) - COALESCE(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 4

Create an auxiliary query using isnull to replace zero with 0 and then execute the sum of the queries and then the total amount

Answer 5

Uch. This question makes my head hurt. Discrimination functions are your friend, and casemakes it easy to create them in SQL. Just make the problem simple.

select total_credits = sum( case j.credit when 1 then 1 else 0 end ) ,
       total_debits  = sum( case j.debit  when 1 then 1 else 0 end ) ,
       total_delta   = sum( case j.credit when 1 then 1 else 0 end )
                     - sum( case j.debit  when 1 then 1 else 0 end )
from journal j
where j.memberid = 48