# SELECTing percentage and count of votes

I have the following tables structures:

CREATE TABLE IF NOT EXISTS options (
option_id tinyint(4) unsigned NOT NULL,
poll_id mediumint(8) unsigned NOT NULL,
option tinytext NOT NULL,
UNIQUE KEY option_id_poll_id (option_id,poll_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Storing poll options/choices';

CREATE TABLE IF NOT EXISTS questions (
poll_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
deleted bit(1) NOT NULL DEFAULT b'0',
question tinytext NOT NULL,
nick varchar(32) NOT NULL,
dated datetime NOT NULL,
PRIMARY KEY (poll_id),
KEY deleted (deleted)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Table to store poll questions/titles';

CREATE TABLE IF NOT EXISTS votes (
poll_id mediumint(8) unsigned NOT NULL,
option_id tinyint(4) unsigned NOT NULL,
nick varchar(32) NOT NULL,
dated datetime NOT NULL,
UNIQUE KEY poll_id_nick (poll_id,nick)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


I am displaying details for a particular poll_id using the following query:

SELECT
o.option_id AS option_id,
o.option AS option,
COUNT(v.nick) AS total,
FLOOR(
( COUNT(v.nick) / (SELECT COUNT(1) FROM votes WHERE poll_id = %d) ) * 100
) AS percentage
FROM options o
ON o.poll_id = v.poll_id
AND v.option_id = o.option_id
WHERE o.poll_id = %d
GROUP BY o.option_id
ORDER BY total DESC, o.option_id ASC;


I am not satisfied with the way that I am calculating the percentage votes on each of the available options! %d is place-holder for string.format function.

I'd like a review on the query/table structures above.

• In Oracle, you could use an Analytic Function to calculate this without a subquery, but MySQL doesn't have an equivalent. Your solution may be best. May 12, 2015 at 2:21
• @Scott: From stackoverflow.com/questions/11806528/…, something similar to Oracle Analytic Function: explainextended.com/2009/03/10/… Jul 3, 2015 at 12:01

It took me a while to realize that you must be considering a poll to be a single question (posted on a web page) that allows people to pick from a series of options for that single question.

Some things to consider:

• [options] Consider reversing the index field order. It would seem probable that you will want to query up the options for a poll without specifying the option_id.

• [questions] There appears to be no real point to indexing the deleted bit. When pulling questions up for a poll you can certainly exclude those that are deleted without needing an index.

• [votes] I'm guessing that 'nick' is the login name of the user choosing an option from a single question poll?

• Look at an explain to see if MySQL has optimized your sub-query into a single lookup. If not, do your own explicit query or consider using group by modifiers to do a rollup when you pull up poll stats. Then, in either case, as you load records for a poll (as you'll have some code for web display purposes) do the percentage calculation as you generate the output table.

Finally, it is customary to "select count(*) from tab" when you don't care about the columns involved.