Click here to Skip to main content
15,947,785 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

looking for help to put together some sub-select SQL code.

Sample table data:

VB
-------------------------
RecID | GroupID | User  |
-------------------------
  1   |    1    | John  |
  2   |    1    | Mary  |
  3   |    1    | Fred  |
  4   |    2    | Joe   |
  5   |    2    | Mary  |
  6   |    3    | Fred  |
  7   |    3    | John  |
  8   |    3    | Mary  |
  9   |    4    | Tony  |
  10  |    5    | Fred  |
-------------------------


I want to query the count of a particular set of users without knowing the GroupID.
For example, how many times do (John + mary + Fred) appear in a grouping?
(in the sample above, its twice, but in a different order).

I am very rusty but think it should be a "Select count (*) from [select Grouping]
where User in [set?]

Any thoughts on something efficent?

many thanks,

Allen.
Posted
Updated 17-May-11 4:05am
v2
Comments
Sandeep Mewara 17-May-11 9:58am    
(in the sample above, its twice, but in a different order).
I don't see how you got this! :doh:
Unclear...
DataBytzAI 17-May-11 10:07am    
Thanks Mewara - I made a mistake in the sample data!! ... well spotted!!

I have fixed it now. In the table there are two sets of GroupID that John + Mary + Fred appear in,
namely GroupID 1 and GroupID 3. I am trying to put together an SQL statement that will tell me the Count of the groups they appear in, ie: 2

1 solution

solved by Kent on MSDN

!declare @test table
( recId integer,
GroupId integer,
[User] varchar(9))
insert into @test
select 1 , 1, 'John' union all
select 2 , 1, 'Mary' union all
select 3 , 1, 'Fred' union all
select 4 , 2, 'Joe ' union all
select 5 , 2, 'Mary' union all
select 6 , 3, 'Fred' union all
select 7 , 3, 'John ' union all
select 8 , 3, 'Mary' union all
select 9 , 4, 'Tony' union all
select 10, 5, 'Fred'

select
GroupId
from @test
where [user] in ('John','Mary','Fred')
group by groupId
having count(distinct [user]) = 3

/* -------- Output: --------
GroupId
-----------
1
3

(1 row(s) affected)
*/

select count(*) as Group_Count
from
( select
GroupId
from @test
where [user] in ('John','Mary','Fred')
group by groupId
having count(distinct [user]) = 3
) x

/* -------- Output: --------
Group_Count
-----------
2
*/
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900