# Complex query to count votes with a redistribution system - follow-up

I've previously posted a question about simplifying a query to count votes with a distribution system. One of the contributors, @200_success, pointed out that it calculated the surplus redistribution based on the second favored candidates original votes rather than their second rank on the winners ballots.

How do I simplify it in the best possible way? This is the resulting table with its queries:

 +-----------+-------+---------------+----------------+----------------------------+
| CANDIDATE | VOTES | RANKED_CHOICE | REDISTRIBUTION | VOTES_AFTER_REDISTRIBUTION |
+-----------+-------+---------------+----------------+----------------------------+
|         1 |     8 |             0 |             -1 |                          7 |
|         2 |     1 |             4 |            0.5 |                        1.5 |
|         3 |     2 |             1 |          0.125 |                      2.125 |
|         4 |     4 |             2 |           0.25 |                       4.25 |
|         5 |     2 |             1 |          0.125 |                      2.125 |
|         6 |     3 |             0 |              0 |                          3 |
+-----------+-------+---------------+----------------+----------------------------+

SELECT vote_candidate candidate, original_votes votes, ranked_choice, surplus_redistribution redistribution, (original_votes + surplus_redistribution) votes_after_redistribution
FROM (
SELECT c.vote_candidate, (
SELECT (
(MAX(votes_above_the_threshold) - (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)) / MAX(votes_above_the_threshold)
) ratio
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
) t
WHERE votes_above_the_threshold = (
SELECT MAX(votes_above_the_threshold)
FROM vote_orders
)
) surplus_ratio, c.original_votes, '0' ranked_choice, LEAST(0,t.threshold - c.original_votes) surplus_redistribution
FROM (
SELECT o.vote_candidate, COUNT(*) original_votes
FROM vote_orders o
WHERE o.vote_order = 1
GROUP BY o.vote_candidate
) c
CROSS JOIN (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) AS threshold
FROM votes
) t
GROUP BY c.vote_candidate
UNION ALL
SELECT vote_candidate, (
SELECT (
(
SELECT (
(MAX(votes_above_the_threshold) - (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)) / MAX(votes_above_the_threshold)
) ratio
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
) t
WHERE votes_above_the_threshold = (
SELECT MAX(votes_above_the_threshold)
FROM vote_orders
)
)
) ratio
) surplus_ratio, d.original_votes, COUNT(*) ranked_choice, (
ROUND((COUNT(*) * (
SELECT (
(
SELECT (
(MAX(votes_above_the_threshold) - (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)) / MAX(votes_above_the_threshold)
) ratio
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
) t
WHERE votes_above_the_threshold = (
SELECT MAX(votes_above_the_threshold)
FROM vote_orders
)
)
) ratio
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders a
INNER JOIN
(
SELECT vote_id, MIN(vote_order) AS min_vote_order
FROM vote_orders
WHERE vote_candidate NOT IN ((
SELECT vote_candidate
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING (
votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
OR (
votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
AND votes_above_the_threshold = 0
)
OR (
votes_above_the_threshold = 0
)
)
) t
))
GROUP BY vote_id
) b
ON a.vote_id = b.vote_id
AND a.vote_order = b.min_vote_order
INNER JOIN
(
SELECT vote_id
FROM vote_orders
WHERE vote_candidate = (
SELECT vote_candidate
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
) t
WHERE votes_above_the_threshold >= (
SELECT MAX(votes_above_the_threshold)
FROM vote_orders
)
)
AND vote_order = 1
) c
ON a.vote_id = c.vote_id
GROUP BY vote_candidate
) t
WHERE votes_above_the_threshold >= (
SELECT MAX(votes_above_the_threshold)
FROM vote_orders
)
LIMIT 1
)), 3)
) surplus_redistribution
FROM vote_orders a
INNER JOIN
(
SELECT vote_id, MIN(vote_order) AS min_vote_order
FROM vote_orders
WHERE vote_candidate NOT IN ((
SELECT vote_candidate
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING (
votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
OR (
votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
AND votes_above_the_threshold = 0
)
OR (
votes_above_the_threshold = 0
)
)
) t
))
GROUP BY vote_id
) b
ON a.vote_id = b.vote_id
AND a.vote_order = b.min_vote_order
INNER JOIN
(
SELECT vote_id
FROM vote_orders
WHERE vote_candidate = 1
AND vote_order = 1
) c
ON a.vote_id = c.vote_id
LEFT OUTER JOIN
(
SELECT vote_candidate o, COUNT(*) AS original_votes
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) d
ON a.vote_candidate = d.o
GROUP BY vote_candidate
ORDER BY surplus_redistribution DESC
) y
GROUP BY vote_candidate;


Table schema:

CREATE TABLE votes
(
vote_id INT NOT NULL AUTO_INCREMENT,
vote_candidate_a INT,
vote_candidate_b INT,
vote_candidate_c INT,
vote_candidate_d INT,
vote_candidate_e INT,
vote_candidate_f INT,
PRIMARY KEY vote_id(vote_id)
);

INSERT INTO votes
VALUES
(NULL, 1, 3, 2, 5, 4, 6),
(NULL, 1, 2, 4, 6, 3, 5),
(NULL, 5, 3, 2, 1, 4, 6),
(NULL, 6, 1, 5, 3, 4, 2),
(NULL, 2, 3, 5, 6, 1, 4),
(NULL, 4, 1, 6, 3, 2, 5),
(NULL, 3, 2, 6, 1, 5, 4),
(NULL, 4, 3, 1, 6, 2, 5),
(NULL, 1, 2, 4, 3, 6, 5),
(NULL, 1, 5, 3, 2, 4, 6),
(NULL, 4, 5, 6, 2, 3, 1),
(NULL, 1, 4, 2, 3, 5, 6),
(NULL, 1, 2, 3, 4, 5, 6),
(NULL, 3, 6, 5, 1, 4, 2),
(NULL, 1, 2, 3, 4, 5, 6),
(NULL, 6, 5, 4, 3, 2, 1),
(NULL, 4, 3, 1, 5, 6, 2),
(NULL, 6, 3, 1, 2, 5, 4),
(NULL, 1, 4, 6, 3, 2, 5),
(NULL, 5, 3, 6, 4, 2, 1);

CREATE TABLE vote_orders
(
id INT NOT NULL AUTO_INCREMENT,
vote_id INT,
vote_order INT,
vote_candidate INT,
PRIMARY KEY id(id)
);

INSERT INTO vote_orders (id, vote_id, vote_order, vote_candidate)
SELECT NULL, vote_id, 1, vote_candidate_a FROM votes
UNION
SELECT NULL, vote_id, 2, vote_candidate_b FROM votes
UNION
SELECT NULL, vote_id, 3, vote_candidate_c FROM votes
UNION
SELECT NULL, vote_id, 4, vote_candidate_d FROM votes
UNION
SELECT NULL, vote_id, 5, vote_candidate_e FROM votes
UNION
SELECT NULL, vote_id, 6, vote_candidate_f FROM votes;

• It's a shame that MySQL does not support Common Table Expressions. I do see you have many levels of nested SELECT statements which makes it difficult to interpret what the code actually does, I would suggest to comment it out to make your life easier. I'll give it a shot for a review here in a bit. Commented Jun 16, 2014 at 19:44
• @Phrancis it supports views Commented Jun 16, 2014 at 19:47

## 2 Answers

It looks better, but I'm still going to recommend using a variable to replace this snippet.

SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes


You could perhaps write a stored procedure to contain the logic as well. While it could also be stored in your schema as a view, it seems a little silly to have a view that returns one record with one field.

This one should also be a stored proedure that returns a single value.

 SELECT (
(MAX(votes_above_the_threshold) - (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)) / MAX(votes_above_the_threshold)
) ratio


Execute your stored procedure once, and store the result in a variable. Try to reduce the layers of nesting. You anytime you reach for cntl+c, stop and consider how you could reuse that particular piece of code.

• This is great advice. I have had troubles using variables as they return errors for me. I guess I need to learn more about them since I do see the advantages they come with. :) Commented Jun 16, 2014 at 21:19
• I wish I could give you advice on that. I really do. I don't know MySQL though. Maybe come up with a simple example of the error you're getting when you try and post it on SO? Commented Jun 16, 2014 at 21:23

One big problem I see with your DDL is that your candidate are multiple columns in votes, rather than being a foreign key to a candidates table. This of itself would make your structure much better and easier to work with.

But the elephant in the room is obvious: SUBQUERIES! Way too many of them!

Another thing, please use -- Comments or /* Comments */ to make your code easier to understand for the next guy who is going to review it. As it is right now, it looks like a wall of SQL code with no rhyme or reason to it.

Listen I would love to review this code, and I have tried, spent several hours in a word editor highlighting different sections, but it is so incredibly convoluted. My suggestion is to start from the beginning but this time bearing your expected results in mind, working from there and fixing syntax and other errors as they come along. Use variables whenever possible for a repeated operation. Best of luck to you.