Sum Over Partition duplicate rows

My query to show transaction and receipt amounts by transaction are duplicating the transaction amounts when multiple receipts are applied to it.
I’m using select distinct and the sum over partition since a transaction might have multiple lines, and I only want to report the total transaction amount here.

select distinct 
    sum (rctl.revenue_amount) over (partition by rcta.trx_number) as "TRX Ammount",
    acra.receipt_number as "Receipt Number"
from
    apps.ra_customer_trx_all rcta,
    apps.ra_customer_trx_lines_all rctl,
    apps.ar_receivable_applications_all araa,
    apps.ar_cash_receipts_all acra
    
where
    rcta.customer_trx_id = rctl.customer_trx_id
    and rcta.trx_number="12345"
    and rcta.customer_trx_id = araa.applied_customer_trx_id
    and araa.cash_receipt_id = acra.cash_receipt_id

When running the query for just the rcta and rctl tables, I get the expected value.

When adding in the araa and acra receipt tables, the value is triple, since the transaction here has three receipts applied to it; I know I’ll get three rows with the same amount, but I want the amount to actually be correct.

I feel like this is probably a basic question, please excuse me as I’m still new to SQL. From reviewing other threads it looks like it involves having a "From (select distinct" coding but I’m not sure how to do this when joining tables.
Thanks!

You need to sign in to view this answers