Rounding Amounts, the missing cent: with the MODEL clause

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.

When we execute the query we get the following results, except for the DIMENSION column (ID) and the MEASURE Indicator all columns are empty.

      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                                          45

It 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] = 100   

This 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         45
   

As 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
      

Links

• Rounding Amounts, the missing cent

Rounding Amounts, the missing cent

Dividing a certain amount over several rows can be quite tricky, simply rounding can lead to differences.
Let me try to explain what I mean. When you need to divide 100 by 3, the answer is 33.333333333333 (and a lot more threes).
Money only goes to cents, so if each one gets 33.33, there is a cent missing. (3 times 33.33 equals 99.99)
To solve this cent-problem, we decide that the difference should be added (or subtracted) on the last row.

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 amount that we want to divide is included in the query on line 2. On line 3 the analytic counterpart of the COUNT(*) function is used to determine the number of records in the resultset. On line 4 you can see the result when you round the amount divided by the number of records in the resultset. All records show 33.33, just as we expected.
Line 5 shows a trick using the LEAD function to identify the last record.

      SQL> select id
        2      ,100 amount
        3      ,count(*) over () entries
        4      ,round (100 / count(*) over (), 2) rounded
        5      ,lead (null, 1, 'x') over (order by id) lastrow
        6    from t
        7  /

       ID            AMOUNT    ENTRIES    ROUNDED L
      ---------- ---------- ---------- ---------- -
              43       100           3      33.33
              44       100           3      33.33
              45       100           3      33.33 x
   

Because we identified the last record in the resultset, it is easy to calculate the difference between the amount that we want to divide and the total of the rounded amount.
In the code below this is done on lines 6 through 9. In plain English it reads: "Take the rounded amount and add to that the difference between the amount and the sum of the rounded amount, but only if you're on the last record"

SQL> select id
  2      ,amount
  3      ,entries
  4      ,rounded
  5      ,sum (rounded) over (order by id) running_rounded
  6      ,rounded + case
  7          when lastrow = 'x'
  8          then amount - sum (rounded) over (order by id)
  9          else 0 end final_amount
 10    from (
 11  select id
 12      ,100 amount
 13      ,count(*) over () entries
 14      ,round (100 / count(*) over (), 2) rounded
 15      ,lead (null, 1, 'x') over (order by id) lastrow
 16    from t
 17  )
 18  /

        ID     AMOUNT    ENTRIES    ROUNDED RUNNING_ROUNDED FINAL_AMOUNT
---------- ---------- ---------- ---------- --------------- ------------
        43        100          3      33.33           33.33        33.33
        44        100          3      33.33           66.66        33.33
        45        100          3      33.33           99.99        33.34

As you can see in the result, the missing cent is added to the last record.
Looking at the query again, I realize that it is not necessary to use the ORDER BY in the SUM function.

Links

• Identifying the last record