SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Transaction number with 2 lines and difference should not repeat twice

AKV27Jun 2 2023

Hi All,

Below is current process.For the the same transaction number 2 line are there and receipt amount for those are 25 and 5..In the accrual column the same amount which is there in the Receipt is coming and that is fine

<table><tbody><tr><td style="height:14.4pt;width:100pt;">Transaction Number</td><td style="width:48pt;">Credit</td><td style="width:48pt;">Receipt</td><td style="width:48pt;">Accrual</td></tr><tr><td style="height:14.4pt;text-align:right;">1234</td><td style="text-align:right;">50</td><td style="text-align:right;">25</td><td style="text-align:right;">25</td></tr><tr><td style="height:14.4pt;text-align:right;">1234</td><td style="text-align:right;">50</td><td style="text-align:right;">5</td><td style="text-align:right;">5</td></tr></tbody></table>

Now What is want is we have added a new column to get the remaining amount for the same transaction number which is Credit - Sum(Receipt) group by Transaction Number.So it will be giving below output

<table><tbody><tr><td style="height:14.4pt;width:100pt;">Transaction Number</td><td style="width:48pt;">Credit</td><td style="width:48pt;">Receipt</td><td style="width:48pt;">Accrual</td><td style="width:48pt;">Diff</td></tr><tr><td style="height:14.4pt;text-align:right;">1234</td><td style="text-align:right;">50</td><td style="text-align:right;">25</td><td style="text-align:right;">25</td><td style="text-align:right;">20</td></tr><tr><td style="height:14.4pt;text-align:right;">1234</td><td style="text-align:right;">50</td><td style="text-align:right;">5</td><td style="text-align:right;">5</td><td style="text-align:right;">20</td></tr></tbody></table>

But What we want is the output in the below format where the difference should come only once.

<table><tbody><tr><td style="height:14.4pt;width:100pt;">Transaction Number</td><td style="width:48pt;">Credit</td><td style="width:48pt;">Receipt</td><td style="width:48pt;">Accrual</td><td style="width:48pt;">Diff</td></tr><tr><td style="height:14.4pt;text-align:right;">1234</td><td style="text-align:right;">50</td><td style="text-align:right;">25</td><td style="text-align:right;">25</td><td style="text-align:right;">20</td></tr><tr><td style="height:14.4pt;text-align:right;">1234</td><td style="text-align:right;">50</td><td style="text-align:right;">5</td><td style="text-align:right;">5</td><td style="text-align:right;">0</td></tr></tbody></table>

Any help would be appreciated.Thank you!!

This post has been answered by BluShadow on Jun 2 2023

Jump to Answer

Added on Jun 2 2023

#answers, #bi, #networking, #pl-sql

2 comments

325 views

1 mention