question

TheBat avatar image
TheBat 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
t-sqlaggregates
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
WilliamD avatar image
WilliamD answered
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
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
@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.
1 Like 1 ·
TheBat avatar image TheBat commented ·
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
0 Likes 0 ·
TheBat avatar image TheBat commented ·
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
0 Likes 0 ·
TheBat avatar image TheBat commented ·
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'.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
@TheBat - sorry about that, I am too used to people being on 2005 and above. I have now supplied the final query in both 2000 and 2005+ versions.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.