tsql distinct having count

Question

I am using SSMS 2008 and am trying to select count of consumers who are part of two different events. Probably this is a simple query, but it is currently not returning expected count. Here is my T-SQL code which better explains what I tried:

select [Program Quarter], event_name, consumer
from #consumer_initiations
where [Program Quarter] = 1  --and consumer = 'Byrd, Victoria Lynn'
group by [Program Quarter], event_name, consumer
having count (distinct event_name) > 1

So this query above returns 0 records. if I run following query, I get all records:

select [Program Quarter], event_name, consumer
from #consumer_initiations
where [Program Quarter] = 1

So I can see where some of these records are where there is same quarter, same person, but 2 or more events for that person. Now, how can I count number of times that same person is part of 2 or more events?

Answer 1

NOTE: Quick off the top of my head, so I am sure this can be optimized:

Break the problem down.

1. Find people who have more than one event for the quarter in question
2. Get the program quarter, event name and consumer for each consumer found in the above subquery

Following this, the subquery is something like

SELECT DISTINCT Consumer
FROM #consumer_initiations
WHERE Count(event_name) > 1

And the full query something like:

SELECT [Program Quarter], event_name, consumer   
FROM #consumer_initiations  
WHERE consumer IN (SELECT DISTINCT Consumer
FROM #consumer_initiations
WHERE Count(event_name) > 1)

I would then think of how to optimize this down without the subquery.