I want to find the percentage for each question’s answer and how many user response to that question
The answer either yes , no, escaped (null)
Table structure :
CREATE TABLE useranswers (
userAnswerID int(10) unsigned NOT NULL auto_increment,
answer varchar(45) default NULL,
userType varchar(45) default NULL,
ques_tesxt varchar(300) default NULL,
ques_Code varchar(25) default NULL,
PRIMARY KEY USING BTREE (`userAnswerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The required Expected result
Question Text
Percentage Responses
Yes 50% 2
No 25% 1
Escaped 25% 1
Total responses: 3
Actually I was able to solve it but by writing more than one query examples
1- select count(answer) from useranswers where userType= 'Student AND answer = 'Yes' // to find how many users select Yes option
2- select count(answer) from useranswers where userType= 'Student AND answer = ‘No’ // to find how many users select this option
3- select count(answer) from useranswers where userType= 'Student AND answer = ‘null ‘ // to find how many users select this option
4- select count(answer) from useranswers where userType= 'UStudent ' AND answer !='null'// to find how many users answer this question
but for the percentage , I need to create view
CREATE VIEW `view1` AS select count(answer) AS `anscount`, answer AS `answer`, userType
from useranswers group by answer, userType
then
1- select coalesce(v.anscount/count(u.answer)*100,0) from view1 v , useranswers u where u.userType= 'Student ' AND u.ques_code = '2' AND u.answer !='null' AND v.userType= 'UStudent ' AND v.ques_code = '2' AND v.answer ='Yes' //to find percentage yes option
2- select coalesce(v.anscount/count(u.answer)*100,0) from view1 v , useranswers u where u.userType= 'Student ' AND u.ques_code = '2' AND u.answer !='null' AND v.userType= 'UStudent ' AND v.ques_code = '2' AND v.answer =No //to find percentage No option
3........
those queries are working but this seems too complicated .
i want them in one query without creating view , i think there is better way to do so thanks