x

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
more ▼

asked Aug 10, 2011 at 02:51 AM in Default

TheBat gravatar image

TheBat
73 7 7 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Aug 10, 2011 at 03:31 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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
Aug 10, 2011 at 04:32 AM TheBat
@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.
Aug 10, 2011 at 06:04 AM WilliamD

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
Aug 10, 2011 at 08:26 AM TheBat

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'.
Aug 11, 2011 at 06:19 AM TheBat
@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.
Aug 11, 2011 at 11:13 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x58

asked: Aug 10, 2011 at 02:51 AM

Seen: 971 times

Last Updated: Aug 10, 2011 at 03:26 AM