Is there an algorithm that can divide a number into three parts and have their totals match the original number?

This version takes precision as a parameter as well:

with q as (select 100 as val, 3 as parts, 2 as prec from dual)select rownum as no      ,case when rownum = parts       then val - round(val / parts, prec) * (parts - 1)        else round(val / parts, prec)       end vfrom   qconnect by level <= partsno  v=== =====1   33.332   33.333   33.34

For example, if you want to split the value among the number of days in the current month, you can do this:

with q as (select 100 as val                 ,extract(day from last_day(sysdate) as parts                 ,2 as prec from dual)select rownum as no      ,case when rownum = parts       then val - round(val / parts, prec) * (parts - 1)        else round(val / parts, prec)       end vfrom   qconnect by level <= parts;1   3.332   3.333   3.334   3.33...27  3.3328  3.3329  3.3330  3.43

To apportion the value amongst each month, weighted by the number of days in each month, you could do this instead (change the level <= 3 to change the number of months it is calculated for):

with q as (  select add_months(date '2013-07-01', rownum-1) the_month        ,extract(day from last_day(add_months(date '2013-07-01', rownum-1)))         as days_in_month        ,100 as val        ,2 as prec  from dual  connect by level <= 3),q2 as (  select the_month, val, prec        ,round(val * days_in_month                      / sum(days_in_month) over (), prec)         as apportioned        ,row_number() over (order by the_month desc)          as reverse_rn  from   q)select the_month      ,case when reverse_rn = 1       then val - sum(apportioned) over (order by the_month                  rows between unbounded preceding and 1 preceding)       else apportioned       end as portionfrom q2;01/JUL/13   33.701/AUG/13   33.701/SEP/13   32.6

Use rational numbers. You could store the numbers as fractions rather than simple values. That's the only way to assure that the quantity is truly split in 3, and that it adds up to the original number. Sure you can do something hacky with rounding and remainders, as long as you don't care that the portions are not exactly split in 3.

The "algorithm" is simply that

100/3 + 100/3 + 100/3 == 300/3 == 100

Store both the numerator and the denominator in separate fields, then add the numerators. You can always convert to floating point when you display the values.

The Oracle docs even have a nice example of how to implement it:

CREATE TYPE rational_type AS OBJECT( numerator INTEGER,  denominator INTEGER,  MAP MEMBER FUNCTION rat_to_real RETURN REAL,  MEMBER PROCEDURE normalize,  MEMBER FUNCTION plus (x rational_type)       RETURN rational_type);

Here is a parameterized SQL version

  SELECT COUNT (*), grp    FROM (WITH input AS (SELECT 100 p_number, 3 p_buckets FROM DUAL),               data               AS (    SELECT LEVEL id, (p_number / p_buckets) group_size                         FROM input                   CONNECT BY LEVEL <= p_number)          SELECT id, CEIL (ROW_NUMBER () OVER (ORDER BY id) / group_size) grp            FROM data)GROUP BY grp

output:

COUNT(*)    GRP33          133          234          3

If you edit the input parameters (p_number and p_buckets) the SQL essentially distributes p_number as evenly as possible among the # of buckets requested (p_buckets).