I am building a surveying system and have come across a problem when trying to get the aggregated results for a answers to a question using the Group By (Simplyfied Tables and Queries listed below).
Quick run down on how things work. A Client can have many Questionnaires, a Questionnaire can have many Sections, a Section can have many questions. Each Question can have many pre-defined options. From the Questionnaire the User selected on of the pre-defined choices this is then saved into the Table UserQuestionnaireResponse. To get the data I can look at 2 tables UserQuestionnaireResponse and AboutYouQuestionsOptions.
My test question asks: What fruit do you like? -
If I do a query (QUERY 1 BELOW) to get all the users I get the result:
If I do a query (QUERY 2 BELOW) by selecting one or more users, I get just the ones they selected
How can I get the all aggregated values even when selecting one or more users?
GROUP BY AboutYouQuestionsOptions_en.OptionName
My first guess when looking at the code is in the WHERE clause. You have a set of ANDs and one OR. The way that they will be evaluated is probably not what you are wanting.
You should put parentheses around the UserIds:
Otherwise the WHERE clause is evaluating like this:
I guess you are wanting to compare the optiontype and the questionid against all users you chose, right?
I would also take a look to see if the INNER JOIN on
---- EDIT - After a little more info, maybe this is what you are looking for:
I took your explanation to mean, you want to always see all options and then the counts per option for the selected users. That is what the following code does.