CODEWITHSUNDEEP
sql
gbb116
gbb116

Reputation: 121

Subtract Two Columns In SQL

I'm drawing a blank, I have this query that works perfectly, but now i want to only return

TotalCredits - TotalDebits as Difference

Below is the query that produces the two values I need to subtract, I played around with joins but I think i'm way off track.

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

Upvotes: 0

Views: 199

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Put your query into a derived table like this;

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

Upvotes: 1

user2989408
user2989408

Reputation: 3137

Try something like this

SELECT 
    SUM(CASE WHEN CREDIT = 1 THEN TOTALAMOUNT END) as TotalCredits,
    SUM(CASE WHEN DEBIT = 1 THEN TOTALAMOUNT END) as TotalDebits,
    SUM(CASE WHEN CREDIT = 1 THEN TOTALAMOUNT END) - SUM(CASE WHEN DEBIT = 1 THEN TOTALAMOUNT END) as Diff
FROM journal 
WHERE memberid=48

Upvotes: 3

Related Questions