# 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:

 +-----------+-------+---------------+----------------+----------------------------+
+-----------+-------+---------------+----------------+----------------------------+
|         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 |
+-----------+-------+---------------+----------------+----------------------------+

FROM (
SELECT c.vote_candidate, (
SELECT (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
) ratio
FROM (
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
)
) t
FROM vote_orders
)
FROM (
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
) t
GROUP BY c.vote_candidate
UNION ALL
SELECT vote_candidate, (
SELECT (
(
SELECT (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
) ratio
FROM (
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
)
) t
FROM vote_orders
)
)
) ratio
) surplus_ratio, d.original_votes, COUNT(*) ranked_choice, (
ROUND((COUNT(*) * (
SELECT (
(
SELECT (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
) ratio
FROM (
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
)
) t
FROM vote_orders
)
)
) ratio
FROM (
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 (
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
)
OR (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
)
)
OR (
)
)
) 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 (
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
)
) t
FROM vote_orders
)
)
AND vote_order = 1
) c
ON a.vote_id = c.vote_id
GROUP BY vote_candidate
) t
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 (
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
)
OR (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
)
)
OR (
)
)
) 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)
);

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. Jun 16, 2014 at 19:44
• @Phrancis it supports views
– Malachi
Jun 16, 2014 at 19:47

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

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


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 (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) 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. :) 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? 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.