Aggregate Problem with selected UserID's

Question

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

Answer 1

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

Aggregate Problem with selected UserID's

Follow this question

Related Questions