I have a table that has a unique id for each question:

How I should return the average selection for each question? for example for question 65 I want to show that 50 % is Option A and 50 % is Option C as like that for question 101 it return that 100% is Option B

CodePudding user response：

Try this:

select distinct question_id,
concat(selected_option,' has a select percentage of ',
    (select  truncate(count(case selected_option when q.selected_option then 1 end) /count(selected_option)*100,0) 
     from questions
     where question_id=q.question_id
     group by question_id), -- this correlated query calculates the percentage for the main query's selected_option of its associated question_id
     '%'
) as percentage
from questions q
;

-- result set:
65, Option A has a select percentage of 50%
101, Option B has a select percentage of 100%
65, Option C has a select percentage of 50%

-- let's make it more interesting:
insert into questions values(65,'Option C'),(65,'Option D');

-- execute the query again and the result set will be:
65, Option A has a select percentage of 25%
101, Option B has a select percentage of 100%
65, Option C has a select percentage of 50%
65, Option D has a select percentage of 25%