Force Balance a + b + c = 100

The requirement is to display three integer percentage values a, b and c, where a + b + c always equals 100.

The values a and b come from table t, and c is calculated as the remainder 100 - a - b.

The problem is, the values of a and b may be too large, causing a + b to exceed 100. In this case the values of a and/or b must be lowered so that a + b = 100 (thus making c = 0).

Here are nine combinations of a and b, where 4 combinations are OK and five combinations exhibit the problem:

CREATE TABLE t ( pkey INTEGER PRIMARY KEY, a INTEGER, b INTEGER ); INSERT t VALUES ( 1, 0, 0 ); INSERT t VALUES ( 2, 10, 10 ); INSERT t VALUES ( 3, 100, 0 ); INSERT t VALUES ( 4, 0, 100 ); INSERT t VALUES ( 5, 55, 55 ); INSERT t VALUES ( 6, 200, 0 ); INSERT t VALUES ( 7, 0, 200 ); INSERT t VALUES ( 8, 90, 70 ); INSERT t VALUES ( 9, 150, 50 ); COMMIT; SELECT pkey, a, b, 100 - a - b AS c FROM t ORDER BY pkey; pkey a b c ----------- ----------- ----------- ----------- 1 0 0 100 2 10 10 80 3 100 0 0 4 0 100 0 5 55 55 -10 6 200 0 -100 7 0 200 -100 8 90 70 -60 9 150 50 -100

The trick is, how should the new "lowered" a and/or b values be calculated?

What's your solution? (don't peek!)

This is a real-world problem taken from the development of a new feature in Foxhound Version 3: The "Busy Wait Idle %" column will show the relative amount of time each connection has spent doing work, waiting for resources, and sitting idle with nothing to do.

Busy + Wait + Idle must add up to 100 because, well, this is the real world, but the SQL Anywhere performance properties used to calculate Busy and Wait aren't always [cough] in step... sometimes Busy + Wait exceeds 100, hence the need to "pull them down" so Busy + Wait = 100.

One way is to recalculate a and b as percentages of ( a + b ):

SELECT pkey, IF t.a + t.b <= 100 THEN t.a ELSE CAST ( ( t.a * 100 ) / ( t.a + t.b ) AS SMALLINT ) END IF AS a, IF t.a + t.b <= 100 THEN t.b ELSE 100 - a END IF AS b, 100 - a - b AS c FROM t ORDER BY pkey; pkey a b c ----------- ----------- ----------- ----------- 1 0 0 100 2 10 10 80 3 100 0 0 4 0 100 0 5 50 50 0 6 100 0 0 7 0 100 0 8 56 44 0 9 75 25 0

This process is sometimes called "force balance" where a column of rounded numbers are fiddled, er, adjusted so they add up to a known total (like 100%) rather than displaying some lame explanation about "rounding errors"... everyone knows that rounded numbers are inherently imprecise, so as long as the adjustment is made in a sensible manner it's sometimes better than displaying numbers that don't add up.

The force balance process often requires row-by-row processing; in the example shown here, it's nice to be able to embed the calculations in a query.

---