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