I've setup a little custom survey with this particular table in which the solutions from the customers are saved:
useranswers - id - user_id - question_id - answer_option - answer_value
Because a few of the questions are multiple choice yet others aren't I've selected two fields rather than someone to keep selected options (answer_option and answer_value). Only checkboxes and radio buttons are utilized so answer_value is definitely 1 or and answer_option has got the id from the checkbox / radio item.
Ok now what Let me do is really a benchmark testing on for instance question_id 1. For instance I wish to visit a area of the number of customers have selected either 'option_a' or 'option_b'. However, I am quite confused at this time how to achieve that? Can you really do this in a single query and when just how?
Because a few of the customers can leave throughout laptop computer I am unable to count the customers to possess that because the total value. And So I did the next to obtain the overall value:
SELECT COUNT(*) FROM `useranswers` WHERE `question_id` = '1' AND answer_option = 'option_a'
This provides me the entire customers which have took part in question_id 1. Now now you ask , how do you count the quantity of customers which have selected 'option_a' or 'option_b'.
After I make use of this query:
SELECT FROM useranswers WHERE (answer_option = 'option_a' AND answer_value = 1) OR (answer_option = 'option_b' AND answer_value = 1)
However, this question provides me with back 4 while you will find three customers only. I figured it might be solved with the addition of an organization BY user_id, but that did not exercise. The creation of that where three rows, that is correct, however i wanted one value back.
Btw utdvalue is definitely unique so for this reason I did not use question_id within the second query
Now the truth is when I recieve the 2nd query fixed, I can calculate the share in PHP, but that will take me two queries to achieve that. So I'm wondering if anybody has some suggestion of methods to get it done better and perhaps to get it done in a single query.
Sorry like a late-comer for this option... but Ought to be easy, but could be easier to see some sample data to know individuals which are checkbox options versus radio (multiple-options) button. Use a SUM( IF() ) construct for every "condition" you would like the counts from for example...
SELECT sum( if( ua.answer_option = 'option_a', 1, 0 )) as OptionACount, sum( if( ua.answer_option = 'option_b', 1, 0 )) as OptionBCount, count(*) as TotalRecordsSelected from userAnswers ua where ua.question_id = 1 and ua.answer_value = 1 and ua.answer_option in ( 'option_a', 'option_b' )
The WHERE clause is clearly likely to look limited to question 1 in which the answer value is 1 (assume 1 = YES or Checked), and also you only worry about 'option_a' or 'option_b'.
The Area selection does a quantity() for each row. The IF() within the sum tests which qualifier it's counting for... option "A" or "B" correspondingly. I am sure it might have more complex with various radio buttons too, however the concept could be similar... you might have to expand the IF() construct to incorporate the
SUM( IF( answer_option = 'radio_optionX' and answer_value = 1, 1, 0 )) as RadioOpXAnswer1Count, SUM( IF( answer_option = 'radio_optionX' and answer_value = 2, 1, 0 )) as RadioOpXAnswer2Count, SUM( IF( answer_option = 'radio_optionX' and answer_value = 3, 1, 0 )) as RadioOpXAnswer3Count, etc...