mysql - Unknown column in 'IN/ALL/ANY subquery'

Question

I have following table named 'votes', where participant's upvote(1) and downvote(-1) stores. I want to get top 3 voted participant ids and its total votes, where total vote = upvote - downvote. I wrote query as given below but getting error : "Unknown column 'total_votes' in 'IN/ALL/ANY subquery'"

table 'votes'
---------------------------------
| participant_id | vote| voter_id
+------+-------+---------+------+
|              1 | 1   |  1
|              2 | 1   |  1
|              3 | 1   |  1
|              4 | -1  |  1
|              5 | 1   |  1

|              1 | -1  |  2
|              2 | 1   |  2
|              3 | 1   |  2
|              4 | 1   |  2
|              5 | 1   |  2

|              1 | 1   |  3
|              2 | 1   |  3
|              3 | -1  |  3
|              4 | -1  |  3
+------+-------+---------+-----+

SELECT `participant_id`, SUM( `vote` ) AS total_votes FROM `votes` 
WHERE total_votes IN  
(SELECT total_votes FROM  
(SELECT DISTINCT (SUM( vote )) AS total_votes FROM  `votes` GROUP BY  `participant_id` 
ORDER BY `total_votes` DESC LIMIT 0 , 3) AS temp )  
GROUP BY `participant_id`

Expected result would be
-------------------------------
| participant_id | total_votes
+------+-------+--------+------+
|              2 | 3   
|              5 | 2   
|              1 | 1   
|              3 | 1   
+------+-------+--------+------+

Answer 1

Check this simple query:

SELECT 
participant_id, total_votes 
FROM 
(SELECT participant_id,SUM(vote) total_votes 
FROM votes 
GROUP BY participant_id) a 
ORDER BY total_votes DESC 
LIMIT 3;

To get all participants for top 3 voting you can use below query:

SELECT 
a.participant_id, b.total_votes 
FROM 
(SELECT participant_id, SUM(vote) total_votes 
FROM votes 
GROUP BY participant_id) a 
JOIN 
(SELECT participant_id,SUM(vote) total_votes 
FROM votes 
GROUP BY participant_id 
ORDER BY total_votes DESC LIMIT 3) b
ON a.total_votes=b.total_votes 
ORDER BY total_votes DESC;

Answer 2

You can rewrite your query by using join to get the top participants whose vote score lies in top 3 votes i.e top 3 votes are (3,2,1)

SELECT t.* FROM
(SELECT `participant_id`, SUM( `vote` ) AS total_votes 
FROM `votes` 
GROUP BY `participant_id`
) t
JOIN  (SELECT SUM( `vote` ) AS total_votes FROM `votes` 
GROUP BY `participant_id`
ORDER BY `total_votes` DESC LIMIT 0 , 3 ) t1
USING(total_votes)
ORDER BY t.total_votes DESC

Fiddle Demo

Answer 3

What you want to do is following, as there is no total_votes column in your table:

SELECT `participant_id`, SUM( `vote` ) AS total_votes FROM `votes` 
WHERE votes IN  
(SELECT total_votes FROM  
(SELECT DISTINCT (SUM( vote )) AS total_votes FROM  `votes` GROUP BY  `participant_id` 
ORDER BY `total_votes` DESC LIMIT 0 , 3) AS temp )  
GROUP BY `participant_id`