Subquery performing a COUNT DISTINCT on the wrong grouping

Question

I'm fairly new to SQL and have a problem with a subquery that is performing a count distinct on the wrong grouping. I'd appreciate any help at all with this.

I have attendees at sessions for a particular group that I am querying for a MS SQL Server (SSRS 2008) Report.

I am trying to join TblGroup, TblGroupSession and TblGroupSUAttendee and count the DISTINCT number of GroupSUAttendee at any GROUP. The query below is counting the distinct number of GroupSUAttendee at any SESSION, so when I add the counts together for a group I am getting duplicates if a TblGroupSUAttendee has attended more than one session.

I need to keep one row per session in the query as I need that for other purposes, but it is fine for each session row to show the complete total of TblGroupSUAttendees for that group as I can reference that value once per group in my SSRS report.

Thoughts/advice/pointers much appreciated. Thanks Eils

SELECT 
  TblGroup.GroupId
  ,TblGroupSession.GroupSessionId
  ,TblGroupSession.GroupSessionDate
  ,TblGroupSUAttendee.GroupSUAttendeeCount

FROM
  TblGroup
  LEFT OUTER JOIN TblGroupSession
    ON TblGroup.GroupId = TblGroupSession.GroupSessionGroupId

LEFT OUTER JOIN (select COUNT(DISTINCT GroupSUAttendeeId) AS GroupSUAttendeeCount,
                    GroupSUAttendeeGroupSessionId
                    FROM TblGroupSUAttendee
                    GROUP BY GroupSUAttendeeGroupSessionId) as TblGroupSUAttendee ON GroupSUAttendeeGroupSessionId = TblGroupSession.GroupSessionId

WHERE
GroupSessionDate >= @StartDate AND GroupSessionDate <= @EndDate

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-03-25T10:57:14

If you want to count attendees within groups, then use group by, but don't include per-session information. In other words, just combine the groups with the sessions, and the sessions with the attendees in one query. Then aggregate by GroupId and count the attendees:

SELECT g.GroupId,
       COUNT(DISTINCT GroupSUAttendeeId) AS GroupSUAttendeeCount
FROM TblGroup g LEFT OUTER JOIN 
     tblGroupSession gs
     ON g.GroupId = gs.GroupSessionGroupId LEFT OUTER JOIN
     TblGroupSUAttendee ga
     ON ga.GroupSUAttendeeGroupSessionId = gs.GroupSessionId
GROUP BY g.GroupId;

Subquery performing a COUNT DISTINCT on the wrong grouping

1 Answers