CODEWITHSUNDEEP
sql-serverjoincountsql-server-2012grouping
user176504
user176504

Reputation: 313

SQL Query to group/count/summarize data with a join in tables

I have the following table structure:

tables

I want to end up with a table that contains a count for each combination of GroupId (possible two values) and Cluster (possible two values) for each u.MemberUserSetRef.

If I do this:

SELECT        u.MemberUserSetRef, COUNT(r1_1.UserSurveyResultRef) AS count1_1
FROM            [User] AS u INNER JOIN
                         UserSurvey AS s ON u.UserRef = s.UserRef LEFT OUTER JOIN
                         UserSurveyResult AS r1_1 ON s.UserSurveyRef = r1_1.UserSurveyRef
WHERE        (r1_1.GroupId = 'Group1') AND (r1_1.Cluster = 1)
GROUP BY u.MemberUserSetRef

I get three rows, one for each unique MemberUserSetRef, containing the count of UserSurveyResults where GroupId is 'Group1' and Cluster is 1.

What I would like to do is return another column (count1_2) for where the GroupId is 'Group1' and Cluster is 2.

I tried this:

SELECT        u.MemberUserSetRef, COUNT(r1_1.UserSurveyResultRef) AS count1_1, COUNT(r1_2.UserSurveyResultRef) AS count1_2
FROM            [User] AS u INNER JOIN
                         UserSurvey AS s ON u.UserRef = s.UserRef RIGHT OUTER JOIN
                         UserSurveyResult AS r1_1 ON s.UserSurveyRef = r1_1.UserSurveyRef RIGHT OUTER JOIN
                         UserSurveyResult AS r1_2 ON s.UserSurveyRef = r1_2.UserSurveyRef
WHERE        (r1_1.GroupId = 'Group1') AND (r1_1.Cluster = 1) AND (r1_2.GroupId = 'Group1') AND (r1_2.Cluster = 2)
GROUP BY u.MemberUserSetRef

But I get no results in this instance.

Could you please point me in the right direction, so I can get a table that looks like this:

MemberUserSetRef|count1_1|count1_2|count2_1|count2_2

containing the appropriate counts for each MemberUserSetRef.

Thank you.

Upvotes: 0

Views: 617

Answers (2)

brak_danych
brak_danych

Reputation: 9

Select T1.MemberUserSetRef,T1.count1_1,T1.count2_1
From 
    (SELECT u.MemberUserSetRef, COUNT(r1_1.UserSurveyResultRef) AS count1_1
    FROM [User] AS u INNER JOIN
         UserSurvey AS s ON u.UserRef = s.UserRef LEFT OUTER JOIN
         UserSurveyResult AS r1_1 ON s.UserSurveyRef = r1_1.UserSurveyRef
    WHERE (r1_1.GroupId = 'Group1') AND (r1_1.Cluster = 1)
    GROUP BY u.MemberUserSetRef) T1,
    (SELECT u.MemberUserSetRef, COUNT(r1_1.UserSurveyResultRef) AS count1_1
    FROM [User] AS u INNER JOIN
         UserSurvey AS s ON u.UserRef = s.UserRef LEFT OUTER JOIN
         UserSurveyResult AS r1_1 ON s.UserSurveyRef = r1_1.UserSurveyRef
    WHERE (r1_1.GroupId = 'Group1') AND (r1_1.Cluster = 2)
    GROUP BY u.MemberUserSetRef) T2
Where T1.MemberUserSetRef=T2.MemberUserSetRef

Upvotes: 1

Shantanu Gupta
Shantanu Gupta

Reputation: 21108

You can take a look about GROUPING SETS in detail. CODEPROJECT

SELECT        u.MemberUserSetRef, COUNT(r1_1.UserSurveyResultRef) AS count1_1, COUNT(r1_2.UserSurveyResultRef) AS count1_2
FROM            [User] AS u INNER JOIN
                         UserSurvey AS s ON u.UserRef = s.UserRef RIGHT OUTER JOIN
                         UserSurveyResult AS r1_1 ON s.UserSurveyRef = r1_1.UserSurveyRef RIGHT OUTER JOIN
                         UserSurveyResult AS r1_2 ON s.UserSurveyRef = r1_2.UserSurveyRef
WHERE        (r1_1.GroupId = 'Group1') AND (r1_1.Cluster = 1) AND (r1_2.GroupId = 'Group1') AND (r1_2.Cluster = 2)
GROUP BY GROUPING SETS 
 ((u.MemberUserSetRef), (u.MemberUserSetRef, s.UserRef),
 (u.MemberUserSetRef, s.UserRef, r1_1.UserSurveyRef)
 )

Upvotes: 0

Related Questions