driz - 1 year ago 117
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)
sum(case when vote=1 and recruit_id=49631 then 1 else 0 end) as total_yes from votes;
``````

this returns

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

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

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 `null`s:
``````SELECT total_votes, total_yes, total_yes * 100 / total_votes AS yes_percent