Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.

Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.

To create an example, first let's create a table with only three records in it.

SQL> create table t 2 as 3 select rownum + 42 id 4 from dual 5 connect by level <= 3 6 ; Table created. SQL> select * 2 from t 3 / ID ---------- 43 44 45

In the code below the DIMENSION is the ID from the table. The measures (the "cells" that we can manipulate in the MODEL clause) are used as follows

Amount | The amount that is the result of the division per record |

Total | The amount that we want to divide over the records |

Diff | Placeholder for the difference between the sum of the rounded amounts and the amount that needs to be divided. |

Indicator | We need to know the highest value for the DIMENSION (to add the difference to). Since we can't determine the highest value of the DIMENSION, this MEASURE is used for that. |

SQL> select * 2 from t 3 model 4 dimension by (id) 5 measures (cast (null as number) amount 6 ,cast (null as number) total 7 ,cast (null as number) diff 8 ,id indicator 9 ) 10 rules ( 11 ) 12 / ID AMOUNT TOTAL DIFF INDICATOR ---------- ---------- ---------- ---------- ---------- 43 43 44 44 45 45It is time to add some rules to the MODEL clause.

The first rule is to add the amount which needs to be divided.

total [0] = 100This will add a "magic" row to the resultset with Dimension 0 where the measure column total is filled with 100, the amount that we want to divide.

The reason I call it a "magic" row is, is because it is not in the table and made up. In this row I will store some values that I need to do my calculations and such.

The second rule is

indicator [0] = max (indicator) [any]In this rule I will determine which row is the "last" row, the one with the highest ID.

Next rule is to do the actual calculation: divide the amount by the number of rows in the resultset. Of course don't count the "magic" row, hence the condition id > 0.

amount[id > 0] = round (total[0]/(count(*)[id > 0]), 2)To determine the total of the rounded values, we will use the following rule:

amount[0] = sum (amount)[id > 0]This total amount is also placed on the "magic" row.

Calculating the difference between the amount that we want to divide and the actual divided amount is done in the following rule:

diff[0] = total[cv()] - amount[cv()]The difference is added to the "last" row in the last rule:

amount[indicator[0]] = amount[cv()] + diff[0]To see the complete query in action:

SQL> select * 2 from t 3 model 4 dimension by (id) 5 measures (cast (null as number) amount 6 ,cast (null as number) total 7 ,cast (null as number) diff 8 ,id indicator 9 ) 10 rules ( 11 total [0] = 100 12 ,indicator [0] = max (indicator) [any] 13 ,amount[id> 0] = round (total[0]/(count(*)[id>0]), 2) 14 ,amount[0] = sum (amount)[id>0] 15 ,diff[0] = total[cv()] - amount[cv()] 16 ,amount[indicator[0]] = amount[cv()] + diff[0] 17 ) 18 / ID AMOUNT TOTAL DIFF INDICATOR ---------- ---------- ---------- ---------- ---------- 43 33.33 43 44 33.33 44 45 33.34 45 0 99.99 100 .01 45As you can see in the output above the values are rounded (in the AMOUNT column) and the last row takes the difference.

But also our "magic" row is added to the output, to filter that one out of the resultset simply add a where clause.

SQL> select id 2 ,amount 3 from (select * 4 from t 5 model 6 dimension by (id) 7 measures (cast (null as number) amount 8 ,cast (null as number) total 9 ,cast (null as number) diff 10 ,id indicator 11 ) 12 rules ( 13 total [0] = 100 14 ,indicator [0] = max (indicator) [any] 15 ,amount[id> 0] = round (total[0]/(count(*)[id>0]), 2) 16 ,amount[0] = sum (amount)[id>0] 17 ,diff[0] = total[cv()] - amount[cv()] 18 ,amount[indicator[0]] = amount[cv()] + diff[0] 19 )) 20 where id> 0 order by id 21 / ID AMOUNT ---------- ---------- 43 33.33 44 33.33 45 33.34