How CanI Tie This Two Queries Together?

[txdave35]

Hey guys,

I've been trying to write this query that will report multiple counts and keep hitting brick walls. Basically, I can get the counts in individual queries, but I can't find a way to time them together.

My tables are called COURSE and REGISTRATION-INSTRUCTOR. They share a common field called course id. The course table looks like this:

COURSE ID COURSE NAME SUBJECT HOURS
0, ENG. LITERATURE, ENG, 4
1, GEOLOGY, SCI, 4
2, INTRO TO SQL, COMP, 4
44, WRITING, ENG, 5
559, PHYSICS, SCI, 5

Instructor TABLE

CLASS_SECTION_ID COURSE_ID INSTRUCTOR_ID
2330, 0, 3
2331, 0, 4
3133, 2, 5
3233, 2, 89


I need to report the following:

total number of courses - 5
total number of instructors per subject - 4


I tried inner joining the two tables on course_id, but that messes up by course count. Since there are multiple classes for the same course, the count is higher than 5 now.

I have these two queries

(select count(subject) as course_count from course)





SELECT count(b.instructor_id) as instructor_count, a.subject
FROM Course a,
[Registration-Instructor] b
WHERE a.course_id=b.course_id

group by a.subject

The problem is I can't join these tables on course_id unless I define it in the select statements. And if I define it in the select statements then I have to include it in the group by which breaks down count down to a new level giving me multiple counts per course_id. Any suggestions?

 

[TamarGranor]

What do you want the final result to look like? I don't see how you'd put the total course count into a result set with the count of instructors for each course.

Tamar

 

[gmmastros]

Is this what you're looking for?

SELECT count(Distinct b.instructor_id) as instructor_count, 
       Count(Distinct a.course_id) As course_count,
       Count(Distinct a.subject) As subject_count
FROM Course a, 
[Registration-Instructor] b 
WHERE a.course_id=b.course_id

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom