tsql distinct count subquery

I am using SSMS 2008 and I need to use a subquery to return the count of unique records / client. How do I do this? Currently I am returning all unique records over the whole dataset and not per client. Here is my pseudocode current开发者_高级运维ly:

    SELECT A.Program, A.PEOPLE_ID, K.EVENT_NAME, A.Program2, A.Program3
     (SELECT COUNT(DISTINCT K.EVENT_NAME) 
     FROM #TEMP1 A, evolv_cs.dbo.facility_view F, evolv_cs.dbo.people_x N, event_view K WITH (NOLOCK)
     WHERE F.group_profile_id = A.group_profile_id AND 
        K.event_definition_id = a.event_definition_id AND
        A.people_id = N.people_id
     GROUP BY K.EVENT_NAME) as DistinctEvent
     FROM #TEMP1 A
     JOIN event_view K WITH (NOLOCK) on K.event_definition_id = A.event_definition_id
     WHERE @START_DATE BETWEEN A.Enrolled_Date AND DATEADD(D, 14, A.Enrolled_Date)
     AND (@SERVICE IS NULL OR @SERVICE = K.event_name)
 GROUP BY 
A.Program, A.PEOPLE_ID, K.EVENT_NAME, A.Program2, A.Program3

OK, I edited the above query now. I still want event_name per client.

---

What you want to use here is the GROUP BY clause for your distinct counts, instead of the joined subqueries. The following two queries should give you what you want.

SELECT A.PEOPLE_ID, COUNT(DISTINCT K.EVENT_NAME)
   FROM #TEMP1 A, event_view K WITH (NOLOCK)
   WHERE K.event_definition_id = a.event_definition_id
         AND @START_DATE BETWEEN A.Enrolled_Date AND DATEADD(D, 14, A.Enrolled_Date)
         AND (@SERVICE IS NULL OR @SERVICE = K.event_name)
   GROUP BY A.PEOPLE_ID

SELECT K.EVENT_NAME, COUNT(DISTINCT A.PEOPLE_ID)
   FROM #TEMP1 A, event_view K WITH (NOLOCK)
   WHERE K.event_definition_id = a.event_definition_id
         AND @START_DATE BETWEEN A.Enrolled_Date AND DATEADD(D, 14, A.Enrolled_Date)
         AND (@SERVICE IS NULL OR @SERVICE = K.event_name)
   GROUP BY K.EVENT_NAME

If you need to combine the results of the queries into a single result set, you should be able to do so with a UNION.