Creating a new column using CASE and maybe count

Right now, I have a query that gets me 3 columns.

SELECT a.studentID, a.classdetailID, c.course_title

FROM studentcoursedetails a, classdetails b, course c

WHERE b.classdetailID = a.classdetailID

AND b.courseID = c.courseID

ORDER BY c.course_title, b.classdetailID

What I get is a column that shows the ID of the student that did the course, the ID of the class they were in and the title of the course itself. Something along these lines:

5---8----Airline Security Operations  
4---8----Airline Security Operations  
6---8----Airline Security Operations  
4---9----Airline Security Operations  
8---10---Airline Security Operations  
5---10---Airline Security Operations  
1---1----Airport Design and Construction  
4---1----Airport Design and Construction  
2---1----Airport Design and Construction  
1---2----Airport Design and Construction

What I need is a table that shows me something like this:

course_title                Number of Classes  Number of students
Airline Security Operations   3                   4

I thought of using CASE, but when I think about it, I just get lost. I'd appreciate your help.

The answer, thanks to gbn:

SELECT c.course_title, COUNT( DI开发者_如何学PythonSTINCT a.studentID ) AS "Students in Course" , COUNT( DISTINCT a.classdetailID ) AS "Total Classes"

FROM studentcoursedetails a

JOIN classdetails b ON b.classdetailID = a.classdetailID

JOIN course c ON b.courseID = c.courseID

GROUP BY c.course_title

---

You want to count each distinct occurence per course_title

Like this (with SQL Server column alias syntax) with proper JOIN syntax

SELECT
    c.course_title
    ,COUNT(DISTINCT a.studentID) AS [Number of students that have taken the course]
    ,COUNT(DISTINCT a.classdetailID) AS [Number of Classes]
FROM
    studentcoursedetails a
    JOIN
    classdetails b ON b.classdetailID = a.classdetailID
    JOIN
    course c ON b.courseID = c.courseID
GROUP BY
    c.course_title