Break cell up into rows

[The Little Guy]

Back in 2007 / 2008 I wrote a voting script which stores votes. The problem I have is I save the votes badly. I have 2 columns:

 

- voteTotal (sum of all the votes)

- votes (number of people who voted)

 

Here are two values of on item:

1394

431

 

I want to break these up into single Items. I know I cannot go back into time and get the values, but I would like to calculate what could have been used. So for the above example, I would insert 431 rows into a table, each with a value of 1-5 (int's) all rows adding up to 1394.

 

Any ideas of what I could do for this?

[requinix]

First, if 1*votes = voteTotal then all the votes are 1. If 5*votes = voteTotal then all the votes are 5.

 

Otherwise get the average, which must be between two numbers in the 1-5 range. Call those two numbers "low" and "high".

Take the fractional part of the average and * votes (and round). This is the number of "high" votes. The remainder is of "low" votes.

voteTotal = 1394
votes = 431
1*431 = 431 so not ones
5*431 = 2155 so not fives

1394 / 431 ~ 3.23433 so low=3 and high=4
0.23433 * 431 = 101 so that many votes of 4
The remainder is 431-101 = 330 votes of 3

I can explain the proof if anyone wants.

[The Little Guy]

How do you decide the high?

 

Maybe I am wrong but I don't think this can be done with pure MySQL...

[requinix]

It should be possible. MySQL can calculate the average and then round it up and down for the two vote numbers. You'd have to construct a loop but with a counter @variable you can do it.

 

If the average is 3.x then it's possible to come up with a set of 3 and 4 votes that satisfies the two conditions. That's because any 1, 2, or 5 votes can be "transformed" into 3s and 4s without affecting the grand total or the number of votes. Because there's an average of 3.x, every low/high vote must have a "balancing" set of high/low votes that maintains the average. The math turns those balanced votes into 3s and 4s regardless of what the actual numbers where.

 

The low and high values are just the two numbers on either side of the average. Say the average is 4.x. Start off with some vote close to that average, like 4. You can pick lots of 4s but the average of them will be too low. You need some higher votes, like 5, to raise it. In this case we picked the two numbers on either side of 4.x - you could pick anything really, it's just that it's easier to pick 4 and 5.

 

Not mentioned is how this all only works if there is a possible set of votes to sum to the total and also maintain the average, but this is the case because these numbers aren't just randomly generated: there was a series of real, legal votes that resulted in them. If it weren't then all the math is out the window because it's simply not possible.

[The Little Guy]

Finally found time to implement this, it work terrific! Thanks for the help!