[SOLVED] Total the amount within a group and don't include duplicate values

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  AMOUNT2
1   A     111              12,000   121              23,000
2   A     112              14,000   121              23,000
3   A     113              16,000   121              23,000
4   B     114              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 which is the SubTotal should be 34,000 only if it will not sum the value that has a duplicate value.

I'm using the below code to compute the total amount:

<?sum(current-group()/AMOUNT2)?>

And by the way I group it by name.

Solution