Mysql Calculate Percentage Of Selected Rows

425 views Asked by At

I'm sorry if my question irrelevant. Because I'm not expert of mysql.

I have surveys in my website. If someone votes my survey, i want to show survey results to the voter.

Here is the DB Structure;

surveys Table (Includes Survey Name Description and survey_id)

Surveys

survey_choices Table (Includes Survey Chocies and related with survey_id to Surveys Table)

Survey Choices

I'm trying to calculate only visible survey choices %percentage.

If i have only 1 survey, it's calculating correct results. But if i have more than 1 survey, mysql calculating whole survey_vote_count values of all table.

Here is my MySQL query;

SELECT 
survey_vote_id, survey_vote_name, survey_vote_count, survey_id, 
survey_vote_count * 100 / t.s AS survey_percentage
FROM survey_votes
CROSS JOIN (SELECT SUM(survey_vote_count) AS s FROM survey_votes) t
WHERE visibility = :visibility
AND survey_id = :surveyId
ORDER BY `survey_votes`.`order_id` ASC

How can i calculate for eg only survey_id = 1 and visibility = 1 %percentage = %100?

Any help will greatly appricated.

1

There are 1 answers

1
TigOldBitties On BEST ANSWER

You should add the same condition to your cross join. At the moment, in your select you sum all survey_vote_count without the same where condition (visibility and survey_id.