Joining based on Sum of amount

Question

Basically there are 2 files
File 1 sample

Reference
Amount

AA1
1000

File 2 sample

Reference
Match_No
Side
Amount

AA1
123
Ledger
1000

BB1
123
Statement
500

CC1
123
Statement
500

Now the requirement is using the reference from File 1 extract the Match_No from file 2 where side = 'Ledger'
The script I think for this should be:-
select file2.match_no
from file1 join file2 on file1.reference = file2.reference
where side = 'Ledger'
Now after extracting Match_No where side = Ledger, for the same Match_No extract all the references from file 2 where side ='Statement' and sum of file2.amounts (where side='Statement') = file2.amount(where side = Ledger)

Littlefoot · Answer

This is how I understood what you are saying. See if it helps.
SQL> with
  2  -- Sample data; you have it already & don't type it
  3  file_1 (reference, amount) as
  4    (select 'aa1', 1000 from dual),
  5  file_2 (reference, match_no, side, amount) as
  6    (select 'aa1', 123, 'Ledger'   , 1000 from dual union all
  7     select 'bb1', 123, 'Statement',  500 from dual union all
  8     select 'cc1', 123, 'Statement',  500 from dual
  9    ),
 10  -- Useful code begins here.
 11  -- Query you posted (I added "B.AMOUNT" and used it in line #21)
 12  qyp as
 13    (select b.match_no, b.amount
 14     from file_1 a join file_2 b on a.reference = b.reference
 15     where b.side = 'Ledger'
 16    )
 17  -- The final query
 18  select b.reference
 19  from file_2 b join qyp q on q.match_no = b.match_no
 20  where b.side = 'Statement'
 21    and q.amount = (select sum(c.amount)
 22                    from file_2 c
 23                    where c.match_no = b.match_no
 24                      and c.side = 'Statement'
 25                   );

REF
---
cc1
bb1

SQL>

Joining based on Sum of amount

One Answer

Add your own answers!

Ask a Question