TSQL DISTINCT RECORD COUNT

I am using SSMS 2008 and am trying to get a distinct count of Event_names from my table. But instead, it is returning count = 1 when there are actually 2 records and other invalid values. What I want is the total count / consumer for their number of distinct event_names they are part of. Also, some of the Event Counts for the same consumer are different. But I want one distinct count / consumer. Here is a portion of the data returned by my invalid query:

consumer Program Enrollment Date event_name Event_Ct

B, Tiffany  2010-09-27 12:00:00.000 Comprehensive Clinical Assessment   1
B, Tiffany  2010-09-27 00:00:00.000 Telemedicine Comprehensive Clinical Assessment  1
B, Nickolas Tyan    2010-12-07 15:00:00.000 Comprehensive Clinical Assessment   1
B, Nickolas Tyan    2010-12-07 00:00:00.000 Telemedicine Comprehensive Clinical Assessment  1
B, Jack 2011-06-13 08:30:00.000 Comprehensive Clinical Assessment   1
B, Jack 2011-01-03 00:00:00.000 Medication Management   1
B, Victoria Lynn    2010-11-10 00:00:00.000 Telemedicine Comprehensive Clinical Assessment  3
B, Victoria Lynn    2010-12-28 00:00:00.000 Telemedicine Psychiatric Assessment 3
B, Victoria Lynn    2011-01-07 00:00:00.000 Telemedicine Psychiatric Progress Note  2
B, Victoria Lynn    2011-02-08 00:00:00.000 Telemedicine Psychiatric Progress Note  2

And here is the T-SQL I used for the above:

SELECT consumer, [Program Enrollment Date], event_name, [Program Quarter]
INTO #INITIATIONS
FROM #consumer_initiations   
WHERE consumer IN 
(SELECT DISTINCT Consumer 
FROM #consumer_initiations
GROUP BY Consumer 
HAVING Count(DISTINCT event_name) > 1)
ORDER BY consumer, event_name

SELECT A.consumer, A.[Program Enrollment Date], A.event_name, count(A.event_name)
FROM #INITIATIONS A 
JOIN #INITIATIONS B ON A.consumer = B.consumer AND A.event_name <> B.event_name AND A.[Program Enrollment Date] <> B.[Program Enrollment Date]
GROUP BY A.consumer, A.event_name, a.[Program Enrollment Date]

I also tried this query, which r开发者_JAVA技巧eturned correct counts for consumers with only 2 records, but for those with > 2 records, it returned too high of counts:

SELECT A.consumer, count(A.event_name)
FROM #INITIATIONS A 
JOIN #INITIATIONS B ON A.consumer = B.consumer AND A.event_name <> B.event_name AND A.[Program Enrollment Date] <> B.[Program Enrollment Date]
GROUP BY A.consumer

Thanks to Chris, here is the solution that worked:

Select I.consumer, [Program Enrollment Date], event_name, countPerConsumer.[Number of Events] 
From #INITIATIONS i 
Join (Select Consumer, count(distinct event_name) as [Number of Events] From #INITIATIONS GROUP BY Consumer) countPerConsumer  
    on countPerConsumer.Consumer 

= i.consumer

I think I've grasped what your trying to do. If not I appolgize. something like this should help you:

Select consumer, [Program Enrollment Date], event_name, countPerConsumer.[Number of Events]
From #INITIATIONS i
Join (Select Consumer, count(distinct event_name) as [Number of Events] 
        group by Consumer)countPerConsumer 
    on countPerConsumber.consumer = i.consumer

the idea is your joining a table built on the fly of a count of distinct events for each consumer. you should be able to keep the piece where you build #INITIATIONS and just replace the last query with the above. hope this helps.