CODEWITHSUNDEEP
mysqlsqlselectpercentage
driz
driz

Reputation: 464

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?

Upvotes: 3

Views: 9888

Answers (3)

Mureinik
Mureinik

Reputation: 311308

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

Upvotes: 3

Norbert
Norbert

Reputation: 6084

Slightly depending on the SQL dialect, aliases can be re-used or not. In mysql you are limited on this. The solution is a subquery:

SELECT total_votes,total_yes,sum(total_votes,total_yes,
(total_yes/total_votes)*100) as YESPercent 
FROM (
  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) a;

Upvotes: 1

wogsland
wogsland

Reputation: 9508

Basically you just need to make your original query a subquery to accomplish this:

SELECT total_votes, total_yes, sum(total_votes,total_yes,(total_yes/total_votes)*100) as YESPercent
FROM
(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) as v;

Upvotes: 1

Related Questions