SQL Server joins, groups, and etc. to do simple number crunching

Question

Here's what I'm working with (SQL Server):

• departments: deptID, deptName
• students: studID, studName, deptID
• assignment: studID, courseID, status

Students are assigned to a department and the student record holds the department's ID number. The "assignment" is a link between a student and a course (course not shown) that holds a status (incomplete, passed, not attempted).

I want to list the departments and, with each department, the total number of students in that department and the total number of assignments with each individual status (see example below if this wording is confusing.)

Ideally I'd have one row returned per department. When running queries like this, this is usually a problem I run into.

Example: One department has 5 students and 5 courses. Each student happens to be assigned each of those 5 courses. Only one student has completed all courses, the others have done nothing. Therefore, the totals for this department would be 20 not attempted, 5 passed, 0 incomplete.

I'll be happy with an answer that suggests the SQL at a very abstract level... I just need to know if joins need to be nested or however that's going to work.

Answer 1

This will give you want you need, with the exception of the student per department count.

SELECT d.deptname, a.status, COUNT(a.status)
FROM departments d
JOIN students s ON d.deptid = s.deptid
JOIN assignment a ON s.studid = a.studid
GROUP BY d.deptname, a.status

To add the student per department count, I would probably just do another query. It would be difficult to do it in the same query as you are grouping by the assignment status.

Answer 2

What about something like this?

SELECT 
  D.deptName, A.status, 
  COUNT(*) AS statusCount, 
  COUNT(DISTINCT(S.studID)) AS studCount
    FROM departments AS D
    INNER JOIN students AS S ON D.deptID = S.deptID
    INNER JOIN assignments AS A ON S.studID = A.studID
    GROUP BY D.deptName, A.status

Answer 3

Something like

SELECT departments.deptName,
       sum(case when status = 'attempted' then 1 else 0 end) as attempted_count,
       sum(case when status = 'passed' then 1 else 0 end) as passed_count,
       sum(case when status = 'incomplete' then 1 else 0 end) as complete_count

FROM departments JOIN students JOIN Assignment
GROUP BY departments.deptName

As you just wanted the idea, I did not state the JOIN conditions.