CODEWITHSUNDEEP
sql
gaurus
gaurus

Reputation: 426

Get the credit and the debit from the transaction details using sql

I have table structure like

From|To |Amt   |In_out
A   | B | 100  |0
B   | A | 200  |1
C   | D | 250  |0

where the last column defines the direction of cash flow I want to get the output as

From | To | Amount Transacted | Debited | Credited
A    | B  | 300               |100      | 200
C    | D  | 250               |250      | 0

I tried various queries like

select DISTINCT r.to,r.to,r.Amt,r.In_out
,sum(r.Amt) as credited 
from 
    Records as r 
join 
    Records as s 
    on 
    (r.from <> s.from and r.to == s.from)
where 
    r.from <>s.from 

--GROUP by r.from

can Anyone help me with this?

Upvotes: 0

Views: 486

Answers (3)

Thomas G
Thomas G

Reputation: 10206

Not sure of your RDBMS: Oracle or SQLite.

Anyway here's a way to achieve this in standard SQL

The trick is to inverse the From and To columns if the In_out is 1, in a UNION query, in order to GROUP the pairs of account correctly

SELECT u.From, u.To, SUM(Amt) AS "Amount Transacted",
            SUM(CASE WHEN In_out=0 THEN Amt ELSE 0 END) AS Debited,
            SUM(CASE WHEN In_out=1 THEN Amt ELSE 0 END) AS Credited
FROM
(
    SELECT In_out, From, To, Amt FROM Records WHERE In_Out = 0
    UNION ALL
    SELECT In_out, To, From, Amt FROM Records WHERE In_Out = 1
) u
GROUP BY u.From, u.To

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want greatest() / least():

select least(from, to) from, greatest(from, to) to,
       sum(amt) AmountTransacted,
       sum(case when In_out = 0 then Amt else 0 end) Debited,
       sum(case when In_out = 1 then Amt else 0 end) Credited
from table t
group by least(from, to), greatest(from, to);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

In many databases, you can use least() and greatest():

select least(from, to), greatest(from, to),
       sum(amount),
       sum(case when in_out = 0 and from = least(from, to) or
                     in_out = 1 and to = least(from, to) 
                then amount else 0
           end) as debited,
       sum(case when in_out = 0 and to = least(from, to) or
                     in_out = 1 and from = least(from, to) 
                then amount else 0
           end) as credited
from records
group by least(from, to), greatest(from, to);

If your database doesn't support these functions, you can use case expressions to accomplish the same thing.

Upvotes: 2

Related Questions