How to get a list of names + a count of occurrences relating to the name from another table

Question

Hi,

Hope my subject line was descriptive enough.

Basically, I have a table called Students, and one called Absences. The Students table contains student information (Name, DOB, StudentID etc.), the Absences table contains any absences (Date, Reason, related by StudentID). I want to get a full list of the students, plus a count of their absences.

Sample results would be:

StudentName    Absences
-------------  -----------
Johnny         3
Mark           2
James          1
Anthony        0
Sharon         0

Can someone suggest a way I should tackle this problem?

I would prefer to have a single SQL statement, possibly incorporating some views etc.

Thanks,

Jacob.

Answer 1

select studentname, count(absences)
from student s, absence a
where s.student_id (+) = a.student_id
group by studentname

Answer 2

This will find the number of absences of each student, and sort with most absences first, as in your example.

SELECT 
   studentname, count(absences_id)
FROM
   Students s 
LEFT OUTER JOIN 
   Absences a ON s.student_id=a.student_id
GROUP BY studentname
ORDER BY count(absences_id) DESC, student_name

The LEFT OUTER JOIN is important, since there may be no rows in the absences table for some students. In that case, all fields from Absences for that row will be null, and the count will be zero, since it only counts non-null fields.

If each absence can span a range of dates, e.g. absences due to sick leave or extended holiday, then you would add start_date, end_date to the Absences table and replace COUNT(absences_id) with SUM(DATEDIFF(day, start_date, end_date)), e.g.

SELECT 
   studentname, SUM(ISNULL(DATEDIFF(day, start_date, end_date),0)
FROM
   Students s 
LEFT OUTER JOIN 
   Absences a ON s.student_id=a.student_id
GROUP BY studentname

You can set the ORDER BY clause to SUM(...) but it will improve readability and maintainability to wrap it in another query.

Answer 3

select s.StudentName as StudentName,count(a.*) as Absences from Students s inner join Absences a on s.StudentID=a.StudentID