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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

306 views

1 mention