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

I've used your structure to create some data that will generate some example output, copy and paste into SSMS if you want to.

declare @consumer_initiations table ([Program Quarter] int null, event_name varchar(100) null, consumer varchar(50) null)
insert into @consumer_initiations ([Program Quarter], event_name, consumer) values (1, 'Event 1', 'Byrd')
insert into @consumer_initiations ([Program Quarter], event_name, consumer) values (1, 'Event 2', 'Plane')
insert into @consumer_initiations ([Program Quarter], event_name, consumer) values (2, 'Event 3', 'Train')
insert into @consumer_initiations ([Program Quarter], event_name, consumer) values (3, 'Event 4', 'Stuff')
insert into @consumer_initiations ([Program Quarter], event_name, consumer) values (1, 'Event 5', 'Plane')

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

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

-- Possibly gets more information than needed

select consumer, [Program Quarter], count(event_name) as event_count
from @consumer_initiations
where [Program Quarter] = 1
group by [Program Quarter], consumer
having COUNT(event_name) > 1 

-- Just displays consumers with more than one event

select consumer, event_count from
(
select [Program Quarter], count(event_name) as event_count, consumer
from @consumer_initiations
where [Program Quarter] = 1
group by [Program Quarter], consumer
having COUNT(event_name) > 1 
) as subq

RESULTS

    Program Quarter event_name                                                                                           consumer
--------------- ---------------------------------------------------------------------------------------------------- --------------------------------------------------

(0 row(s) affected)

Program Quarter event_name                                                                                           consumer
--------------- ---------------------------------------------------------------------------------------------------- --------------------------------------------------
1               Event 1                                                                                              Byrd
1               Event 2                                                                                              Plane
1               Event 5                                                                                              Plane

(3 row(s) affected)

consumer                                           Program Quarter event_count
-------------------------------------------------- --------------- -----------
Plane                                              1               2

(1 row(s) affected)

consumer                                           event_count
-------------------------------------------------- -----------
Plane                                              2

(1 row(s) affected)

Answer 2

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.