asked
Aggregate Problem with selected UserID's
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? - 1 Apple 2 Pear 3 Orange 4 Other If I do a query (QUERY 1 BELOW) to get all the users I get the result: Apple 2 Orange 1 Other 0 Pear 0 If I do a query (QUERY 2 BELOW) by selecting one or more users, I get just the ones they selected Apple 2 Orange 1 **How can I get the all aggregated values even when selecting one or more users?** TABLE Client(id (PK), Firstname, Middlename, Lastname) TABLE AboutYouQuestionnaire_en(ID (PK), ClientID, QuestionnarieType, QuestionnarieTitle) TABLE AboutYouSections_en(ID (PK), QuestionnaireID, SectionTitle, SectionOrder) TABLE AboutYouQuestions_en(ID (PK), SectionID, QuestionText, QuestionType, Mandatory, QuestionOrder) TABLE AboutYouQuestionsOptions_en(ID (PK), QuestionID, OptionName, OptionValue, OptionType, IsCorrectAnswer) TABLE Users(id (PK), ClientID, FirstName, Middlename, Lastname) TABLE UsersQuestionnaire(ID (PK), UserID, DateCommenced, DateFinished, Completed) TABLE UserQuestionnaireResponse(UserQuestionnaireID, QuestionID, ResponseBit, ResponseInt, ResponseFloat, ResponseDate, IsCorrect, Weighting, ResponseTime) **QUERY 1** SELECT AboutYouQuestionsOptions_en.OptionName AS Choice, COUNT(UserQuestionnaireResponse.ResponseInt) AS Responses FROM UserQuestionnaireResponse RIGHT OUTER JOIN AboutYouQuestionsOptions_en ON UserQuestionnaireResponse.QuestionID = AboutYouQuestionsOptions_en.QuestionID AND UserQuestionnaireResponse.ResponseInt = AboutYouQuestionsOptions_en.OptionValue WHERE (AboutYouQuestionsOptions_en.QuestionID = 1) AND AboutYouQuestionsOptions_en.OptionType = 0 GROUP BY AboutYouQuestionsOptions_en.OptionName **QUERY 2** SELECT AboutYouQuestionsOptions_en.OptionName AS Choice, COUNT(UserQuestionnaireResponse.ResponseInt) AS Responses FROM UserQuestionnaireResponse INNER JOIN UsersQuestionnaire ON UserQuestionnaireResponse.UserQuestionnaireID =
UsersQuestionnaire.ID RIGHT OUTER JOIN AboutYouQuestionsOptions_en ON UserQuestionnaireResponse.QuestionID = AboutYouQuestionsOptions_en.QuestionID AND UserQuestionnaireResponse.ResponseInt = AboutYouQuestionsOptions_en.OptionValue WHERE (AboutYouQuestionsOptions_en.QuestionID = 1) AND (AboutYouQuestionsOptions_en.OptionType = 0) AND (UsersQuestionnaire.UserID = 2) OR (UsersQuestionnaire.UserID = 3) 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: AND ((UsersQuestionnaire.UserID = 2) OR (UsersQuestionnaire.UserID = 3)) Otherwise the WHERE clause is evaluating like this: - QuestionId=1 AND OptionType =0 AND UserId=2 - OR UserId=3 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 `UserQuestionnaire` is possibly filtering out results. ---- **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.** DECLARE @UserQuestionnaireResponse AS TABLE (UserQuestionnaireID int, QuestionID int, ResponseBit bit, ResponseInt int, ResponseFloat float, ResponseDate datetime, IsCorrect bit, Weighting decimal(5, 2), ResponseTime int) DECLARE @AboutYouQuestionsOptions_en AS TABLE (ID int, QuestionID int, OptionName varchar(20), OptionValue int, OptionType tinyint, IsCorrectAnswer bit) DECLARE @UsersQuestionnaire AS TABLE (ID int, UserID int, DateCommenced datetime, DateFinished datetime, Completed bit) INSERT INTO @UsersQuestionnaire (ID,UserID,DateCommenced,DateFinished,Completed) SELECT 1,1,'20110801','20110801',1 UNION ALL SELECT 2,2,'20110801','20110801',1 UNION ALL SELECT 3,3,'20110801','20110801',1 INSERT INTO @AboutYouQuestionsOptions_en (ID,QuestionID,OptionName,OptionValue,OptionType,IsCorrectAnswer) SELECT 1,1,'Apple',1,0,0 UNION ALL SELECT 2,1,'Pear',2,0,0 UNION ALL SELECT 3,1,'Orange',3,0,0 UNION ALL SELECT 4,1,'Other',4,0,0 INSERT INTO @UserQuestionnaireResponse (UserQuestionnaireID,QuestionID,ResponseBit,ResponseInt,ResponseFloat,ResponseDate,IsCorrect,Weighting,ResponseTime) SELECT 1,1,1,1,NULL,'20110801',1,NULL,NULL UNION ALL SELECT 2,1,1,1,NULL,'20110801',1,NULL,NULL UNION ALL SELECT 3,1,1,3,NULL,'20110801',1,NULL,NULL -- SQL 2000 Version SELECT AYQU.OptionName, ISNULL(counts.Value,0) AS Responses FROM @AboutYouQuestionsOptions_en AYQU LEFT JOIN (SELECT COUNT(UQR.ResponseInt) Value, UQR.QuestionID, UQR.ResponseInt FROM @UserQuestionnaireResponse UQR LEFT JOIN @UsersQuestionnaire UQ ON
UQ.ID = UQR.UserQuestionnaireID WHERE UserId IN (1,2) GROUP BY UQR.QuestionID, UQR.ResponseInt) counts ON counts.QuestionID = AYQU.QuestionID AND counts.ResponseInt = AYQU.OptionValue -- SQL 2005+ Version SELECT AYQU.OptionName, counts.Value FROM @AboutYouQuestionsOptions_en AYQU OUTER APPLY (SELECT COUNT(UQR.ResponseInt) Value FROM @UserQuestionnaireResponse UQR LEFT JOIN @UsersQuestionnaire UQ ON
UQ.ID = UQR.UserQuestionnaireID WHERE AYQU.QuestionID = UQR.QuestionID AND UQR.ResponseInt = AYQU.OptionValue AND UserId IN (1,2)) counts
@TheBat - sorry for not being clear on that. The `INNER JOIN` is filtering out results where there is no entry for that person in the table UserQuestionnaire. If you change it to a LEFT JOIN the rows will get returned, regardless of whether there is an entry in UserQuestionnaire or not.
Hi WilliamD Iam aware of the parentheses (I just copied and pasted it), still doesn't work when using the correct condition even when selecting Just UserID 2 The OptionType is not required for this query, this gives me scope to add more than one list of options to a question. This would be set to either 0 or 1. I would use the QuestionType to determine this. I have trying all the filtering and Join types using VIEWS. just not able to get the aggregated table if I include the UserQuestionnaire Table. I jsut get the ones that have been selected by each users. This would be okay with 1000's of records the chance each option would have been choosen. Regards Kevin
Hi WilliamD I have tried LEFT, RIGHT, INNER's, OUTER's and even made up some. Still no luck. last example SELECT TOP 100 PERCENT dbo.AboutYouQuestionsOptions_en.OptionName, dbo.AboutYouQuestionsOptions_en.OptionValue, COUNT(dbo.UserQuestionnaireResponse.ResponseInt) AS [Total Responses] FROM dbo.AboutYouQuestionsOptions_en LEFT JOIN dbo.UserQuestionnaireResponse ON dbo.AboutYouQuestionsOptions_en.OptionValue = dbo.UserQuestionnaireResponse.ResponseInt RIGHT JOIN dbo.UsersQuestionnaire ON dbo.UserQuestionnaireResponse.UserQuestionnaireID =
dbo.UsersQuestionnaire.ID WHERE ((dbo.AboutYouQuestionsOptions_en.QuestionID = 1) AND (dbo.UsersQuestionnaire.UserID >= 1)) GROUP BY dbo.AboutYouQuestionsOptions_en.OptionName, dbo.AboutYouQuestionsOptions_en.OptionValue ORDER BY dbo.AboutYouQuestionsOptions_en.OptionValue
Hi William I really do appreciate the time you have given me. I am using SQL 2000 and could not get your example to work. so I have clean out all my tables and inserted your data. I did have a issue with the UserQuestionnaireResponse table would not let me delete 2 records even though I had drop all constraints. In the end I drop the table and re-created it. Anyway I have finally come to run your script which and get the following error. I have remove the @ as the query now looks at the data in the tables SELECT AYQU.OptionName, counts.Value FROM AboutYouQuestionsOptions_en AYQU OUTER APPLY (SELECT COUNT(UQR.ResponseInt) Value FROM UserQuestionnaireResponse UQR LEFT JOIN UsersQuestionnaire UQ ON
UQ.ID = UQR.UserQuestionnaireID WHERE AYQU.QuestionID = UQR.QuestionID AND UQR.ResponseInt = AYQU.OptionValue AND UserId IN (1,2)) counts Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'OUTER'. Server: Msg 170, Level 15, State 1, Line 9 Line 9: Incorrect syntax near 'counts'.
