SQL: Using Multiple Aggregates In One SQL Query Statement

How would you get all those aggregates from the second SELECT statement worked into the first SELECT statement? Can this stuff even all be put into one statement?

What I'm trying to end up with is a table listing the Professor, Course, Year, Registration, and then the amount of A's, B's, etc.

What I have is a table listing the Professor, Course, Year, and Registration. I can also get a list with the counts for each grade. But I need to get the two together somehow. Any thoughts?


SELECT (gp.last_name + ', ' + gp.first_name) AS 'Prof',
gs.course,
LEFT(gs.quarter_year,4) AS 'Year',
COUNT(gs.enroll_id) AS 'Reg'
FROM section s INNER JOIN person p
ON p.person_id = s.person_id
INNER JOIN grade_sheets gs ON gs.section_id = s.section_id
WHERE s.quarter_year = 20073
GROUP BY p.last_name, p.first_name, s.course, s.quarter_year
ORDER BY 'Prof'




SELECT
Count_A =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'A' AND gs.section_id = @sectionID),
Count_B =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'B' AND gs.section_id = @sectionID),
Count_C =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'C' AND gs.section_id = @sectionID),
Count_D =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'D' AND gs.section_id = @sectionID),
Count_F =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'F' AND gs.section_id = @sectionID)