title: SQL Server Query - Count on same table twice
body: <p>This problem has been sending me insane, although admittedly I've not written SQL for a long time.</p>
  
  <p>I have 2 tables:</p>
  
  <pre><code>CREATE TABLE [Entities].[Events](
  [EventID] [int] IDENTITY(1,1) NOT NULL,
  [EventVenueID] [int] NULL,
  [EntityID] [int] NOT NULL,
  [OrganisationID] [int] NULL,
  [Title] [nvarchar](300) NOT NULL,
  [DateStart] [datetime] NOT NULL,
  [DateFinish] [datetime] NOT NULL,
  [Notes] [nvarchar](max) NULL,
  [MinimumProviders] [int] NOT NULL,
  [MinimumAttendees] [int] NOT NULL,
  [ShowCalendar] [bit] NOT NULL,
  [CreationDate] [datetime] NOT NULL,
  [IsEnabled] [bit] NOT NULL)
  
  CREATE TABLE [Entities].[EventParticipants](
  [ParticipantID] [int] IDENTITY(1,1) NOT NULL,
  [EventID] [int] NOT NULL,
  [PersonID] [int] NOT NULL,
  [ParticipantType] [int] NOT NULL,
  [ParticipantStatus] [int] NOT NULL,
  [AttendanceStatus] [int] NULL)
  </code></pre>
  
  <p>And a query:</p>
  
  <pre><code>SELECT
  e.EventID,
  ev.VenueName,
  e.EntityID,
  o.Name AS 'Organisation',
  e.Title,
  e.DateStart,
  e.DateFinish,
  e.Notes,
  e.MinimumProviders,
  e.MinimumAttendees,
  e.CreationDate,
  COUNT(epp.ParticipantID) AS 'ProvidersConfirmed',
  COUNT(epa.ParticipantID) AS 'AttendeesConfirmed',
  e.IsEnabled
    FROM
  Entities.Events e
    LEFT OUTER JOIN
  Entities.EventVenues ev
    ON
  e.EventVenueID = ev.EventVenueID
    LEFT OUTER JOIN
  Entities.Organisations o
    ON
  e.OrganisationID = o.OrganisationID
    LEFT OUTER JOIN
  Entities.EventParticipants epp
    ON
  e.EventID = epp.EventID AND epp.ParticipantType = 1 AND epp.ParticipantStatus = 3
    LEFT OUTER JOIN
  Entities.EventParticipants epa
    ON
  e.EventID = epa.EventID AND epa.ParticipantType = 2 AND epa.ParticipantStatus = 3
    GROUP BY
  e.EventID,
  ev.VenueName,
  e.EntityID,
  o.Name,
  e.Title,
  e.DateStart,
  e.DateFinish,
  e.Notes,
  e.MinimumProviders,
  e.MinimumAttendees,
  e.CreationDate,
  e.IsEnabled
  </code></pre>
  
  <p>The count is producing strange results, for example in the EventParticipants table I have the following data:</p>
  
  <p>Participant Type = 1 (3 records)
  Participant Type = 2 (1 record)
  Participant Status = 3 (4 records)</p>
  
  <p>The queries count should return:</p>
  
  <p>ProvidersConfirmed = 3
  AttendeesConfirmed = 1</p>
  
  <p>However, it's returning</p>
  
  <p>ProvidersConfirmed = 3
  AttendeesConfirmed = 3</p>
  
  <p>Can anyone help?</p>