driz driz - 6 months ago 10
MySQL Question

mysql calculate percentage in select

I have a basic voting system that collects votes. Tabulation should take total yes votes divided by total votes to determine if a 2/3rd majority is reached.
Currently, I can return the data using this query

select sum(case when vote is not null and recruit_id=49631 then 1 else 0 end)
as total_votes,
sum(case when vote=1 and recruit_id=49631 then 1 else 0 end) as total_yes from votes;


this returns

+-------------+-----------+
| total_votes | total_yes |
+-------------+-----------+
| 3 | 2 |
+-------------+-----------+


what I would like to do is something like this

+-------------+-----------+-----------+
| total_votes | total_yes | YESPercent|
+-------------+-----------+-----------+
| 3 | 2 | 66.6 |
+-------------+-----------+-----------+


I tried by using something like this:

select sum(case when vote is not null and recruit_id=49631 then 1 else 0 end) as total_votes,
sum(case when vote=1 and recruit_id=49631 then 1 else 0 end) as total_yes,
sum(total_votes,total_yes,(total_yes/total_votes)*100) as YESPercent from votes;


It doesn't recognize the total_yes or total_votes for the final portion.. Any tips or links to good guidance?

Answer

The neatest way to do this, IMHO, would be to have the basic results in a subquery and calculate using them in the outer query. Note that since you're only interested in recruit_id = 49631 in both columns, this condition can be moved to the where clause. It will also, probably, slightly improve the query's performance. As another improvement, you could use the more straight forward count instead sum by using its quality of skipping nulls:

SELECT total_votes, total_yes, total_yes * 100 / total_votes AS yes_percent
FROM   (SELECT COUNT(vote) AS total_votes, 
               COUNT(CASE WHEN vote = 1 THEN 1 END) as total_yes,
         FROM  votes
         WHERE recruit_id = 49631) t
Comments