Total the amount within a group and don't include duplicate values
I'm creating a report in Oracle 12c BI Publisher using a RTF Template and having an issue which is I want a grand total in the bottom of the report which will compute all the amount but will not include the duplicate value. For example I have a table consists of:
| ID | Name | Transaction_NO1 | Amount1 | Transaction_No2 | Transaction_No3 |
|---|---|---|---|---|---|
| ID | Name | Transaction_No1 | Amount1 | Transaction_No2 | Amount2 |
| 1 | A | 1111 | 12,000 | 121 | 23,000 |
| 2 | A | 1112 | 14,000 | 121 | 23,000 |
| 3 | A | 1113 | 16,000 | 121 | 23,000 |
| 4 | B | 1114 | 11,000 | 122 | 11,000 |
| SubTotal | 80,000 |
You can see above that the 121 in column Transaction_No2 has a multiple values but different transaction_no1 so that it Computes all the amount in amount2