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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With