Compare Two Different Fields In Oracle SQL

Question

I have a requirement in which I have two main fields Amount CR and Amount DR. Now the requirement is that this both amounts have different values like Trx Number, Bank Name ETC but have a common Reference Number. There is only one record for every Refrence Number with a CR Amount, DR Amount respectivly.

For detaila see the table below:

Transaction Number	Bank Name	Reference Number	CR Amount	DR Amount
1	XYZ	1234	1000
2	ABC	1234		1000
3	DEF	1111		1000
4	TEST	1111	2300

So basically I want to compare CR and DR Amount based on the Reference Number. In the example Reference Number 1234 is ok and Reference Number 1111 should be listed.

How can I achieve this by an Oracle query?

Answer 1

Add two analytical aggregated functions calculating the sum of CRand DB per the reference_number and compare them

case when
sum(cr_amount) over (partition by reference_number) =
sum(dr_amount) over (partition by reference_number) then 'Y' else 'N' end is_equ

This identifies the rows with reference_number where the sum is not equal.

In an additional query simple filter only the rows where the not equal sum.

with test as (
select a.*,
case when 
sum(cr_amount) over (partition by reference_number) =
sum(dr_amount) over (partition by reference_number) then 'Y' else 'N' end is_equ
from tab a)
select 
  TRANSACTION_NUMBER, BANK_NAME, REFERENCE_NUMBER, CR_AMOUNT, DR_AMOUNT
from test 
where is_equ = 'N'

TRANSACTION_NUMBER BANK REFERENCE_NUMBER  CR_AMOUNT  DR_AMOUNT
------------------ ---- ---------------- ---------- ----------
                 3 DEF              1111                  1000
                 4 TEST             1111       2300