How to get a list of names + the number of entries associated with a name from another table

Question

I hope that my storyline is quite descriptive.

Basically, I have a table called “Students,” and one is called “Absence.” The "Students" table contains information about the student (name, DOB, StudentID, etc.), the Absences table contains any paragraphs (date, reason associated with StudentID). I want to get a complete list of students, as well as an account of their absence.

Examples of results:

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

Can someone suggest a way I should solve this problem?

I would rather have one SQL statement, possibly containing some views, etc.

Thanks,

Jacob.

Answer 1

This will find the number of omissions of each student and will be sorted first with most paragraphs, 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 

LEFT OUTER JOIN is important because in some cases there are no rows in the absences table for some students. In this case, all fields from Absences for this row will be equal to zero, and the counter will be equal to zero, since it takes into account only non-zero fields.

If every absence can cover a date range, for example. absence due to sick leave or extended leave, then you should add start_date, end_date to the Absences table and replace COUNT (absens_id) with SUM (DATEDIFF (day, start_date, end_date)), for example

 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 this will improve readability and maintainability to wrap it in another query.

Answer 2

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

Answer 3

select s.StudentName as StudentName, count (a. *) as "No students" internal connection Missing a on s.StudentID = a.StudentID