Calculating percent of votes inside mysql statement.

Question

    UPDATE polls_options SET `votes`=`votes`+1, `percent`=ROUND((`votes`+1) / (SELECT voters FROM polls WHERE poll_id=? LIMIT 1) * 100,1)
  WHERE option_id=?
    AND poll_id=?

Don't have table data yet, to test it properly. :)

And by the way, in what type % integers should be stored in database?

Thanks for the help!

Answer 1

You don't say what database your using (Postgresql, Mysql, Oracle..etc) but if your using Mysql you could get away with using a TinyInt datatype. Your rounding to an int anyway, and assuming your percentages will always be between 0 and 100 you'll be fine.

Answer 2

Your problem seems to be that you don't have any test data so you are unable to test the syntax of your query. But that is a problem you can easily solve yourself and it doesn't even take that long:

Just make up some data and use that to test.

This isn't as hard as it might sound. For example here I create two polls, the first of which has four votes and the second of which has two votes. I then try to add a vote to option 1 of poll 1 using your query.

CREATE TABLE polls_options (
    poll_id INT NOT NULL,
    option_id INT NOT NULL,
    votes INT NOT NULL,
    percent FLOAT NOT NULL
);
INSERT INTO polls_options (poll_id, option_id, votes, percent) VALUES
(1, 1, 1, '25'),
(1, 2, 3, '75'),
(2, 1, 1, '50'),
(2, 2, 1, '50');

CREATE TABLE polls (poll_id INT NOT NULL, voters INT NOT NULL);
INSERT INTO polls (poll_id, voters) VALUES
(1, 4),
(2, 2);

UPDATE polls_options
SET votes = votes + 1,
    percent = ROUND((votes + 1) / (SELECT voters FROM polls WHERE poll_id = 1 LIMIT 1) * 100,1)
WHERE option_id = 1
AND poll_id = 1;

SELECT * FROM polls_options;

Here are the results:

poll_id option_id votes percent
1       1         2     75
1       2         3     75
2       1         1     50
2       2         1     50

You can see that there are a number of problems:

• The polls table isn't updated yet so the total vote count for poll 1 is wrong (4 instead of 5). Notice that you don't even need this table - it duplicates the same information that can already be found in the polls_options table. Having two keep these two tables in sync is extra work. If you need to adjust the results for some reason, for example to remove some spam voting, you will have to remember to update both tables. It's unnecessary extra work and an extra source of errors.
• Even if you have remembered to update the polls table first, the percentage for option 1 is still calculated incorrectly: it is calculated as 3/5 instead of 2/5 because it is effectively doing this calculation: ((votes + 1) + 1).
• The percentage for 2 isn't updated causing the total percentage for poll 1 to be greater than 100.
• You probably shouldn't even be storing the percentage in the database. Instead of persisting this value consider calculate it on-the-fly only when you need it.

You might want to reconsider your table design to avoid redundant data. Consider normalizing your table structure. If you do this then all the problems I listed above will be solved and your statements will be much simpler.

Good luck!