Compare counts [SOLVED] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Compare counts [SOLVED]

Author

Topic

JBee
Starting Member

9 Posts

Posted - 2008-03-12 : 16:28:49

Hi, hope someone can help. I have two tables

Test1 that lists all the training courses that i can count to find out the total as below.

select count (distinct Training_Course) as total
from Test1

Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.

select Cust_id, count (Attended) as TotalAttend
from Test2
Group by Cust_id

What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.

Make any sense? Any help/suggestions gratefully recieved.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-03-12 : 17:43:32

select Cust_id, count (Attended) as TotalAttend
from Test2
Group by Cust_id having count (Attended)=(select Cust_id, count (Attended) as TotalAttend
from Test2
Group by Cust_id)

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-03-12 : 18:00:36

Lots of ways to do this. I think that last post intended to put your first query in the HAVING clause. Should be:

select Cust_id, count (Attended) as TotalAttend
from Test2
Group by Cust_id having count (Attended)=(select count (distinct Training_Course) from Test1)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-03-12 : 18:20:04

quote:
Originally posted by tprupsis

Lots of ways to do this. I think that last post intended to put your first query in the HAVING clause. Should be:

select Cust_id, count (Attended) as TotalAttend
from Test2
Group by Cust_id having count (Attended)=(select count (distinct Training_Course) from Test1)

lol,I am so sorry.Yes,I am bad with copy and pasting stuff at 4 in the night.my apologies !!

JBee
Starting Member

9 Posts

Posted - 2008-03-12 : 18:21:11

Thank you. Saw Scakets e-mail first and quickly spotted the having count that solved the problem. Thanks tprupsis too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources