CODEWITHSUNDEEP
sql-server
ApatheticLamp
ApatheticLamp

Reputation: 21

How to subtract two values from the same column SQL

I am building a procedure that when given a customerID it will subtract an account's type 2 (Credit card) balance from an account type 1 (Savings) balance, if there is an savings account then it subtracts the credit card balance. (ex savings balance - credit card balance = total balance)

My table is set up like such

ID      Number      Balance     AccountType     CustomerID
-----------------------------------------------------------
1       2434789     451.23      1               1
2       2435656     1425.12     1               2   
3       2434789     12.56       2               1
4       4831567     45894.23    2               2
5       8994785     500.00      2               3
6       4582165     243.10      2               4
7       7581462     1567842.21  1               3
8       2648956     1058.63     2               5
9       4582165     4865.12     1               4
10      4186545     481.56      2               6

I have tried looking this up to get some guidance but everything I have found hasn't quite helped me. If someone can explain or show me what I need to do that would be great, this is the only part of my assignment I am stuck on.

Upvotes: 2

Views: 2667

Answers (2)

ANewGuyInTown
ANewGuyInTown

Reputation: 6437

You could group by CustomerId and get the sum of saving and credit balances

select 
    c.CustomerId, 
    SUM(CASE WHEN AccountType = 1 THEN Balance ELSE 0 END) Saving,
    SUM(CASE WHEN AccountType = 2 THEN Balance ELSE 0 END) Credit,
from 
    Customer c
group by 
     c.CustomerId

And then you can easily get the total with below query: 

Select 
  CustomerId,
  Saving - Credit
from 
(
  select 
      c.CustomerId, 
      SUM(CASE WHEN AccountType = 1 THEN Balance ELSE 0 END) Saving,
      SUM(CASE WHEN AccountType = 2 THEN Balance ELSE 0 END) Credit,
  from Customer c
  group by c.CustomerId
) cust

Upvotes: 3

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

You join the table to itself, where each side of the join only includes the appropriate account type records:

SELECT coalesce(s.CustomerID, cc.CustomerID) CustomerID
      ,coalesce(s.Number, cc.Number) Number
      coalesce(s.Balance,0) -  coalesce(cc.Balance,0) Balance
FROM (SELECT * FROM [accounts] WHERE AccountType = 2) s
FULL JOIN (SELECT * FROM [accounts] WHERE AccountType = 1) cc on cc.customerID = s.customerID

Upvotes: 1

Related Questions