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

I hope my subject line was descriptive enough.

Actually, I call a student, and in a lack that is called student name (name, DOB, StudentID etc.) in the student table, in absence table, any absence (date, reason, Student id related). I want to get a complete list of students, and a number of their absence.

Sample results will be:

 absence of student name ----------- ----------- Johnny 3 Mark 2 James 1 Anthony 0 Sharon 0 

Can someone suggest in such a way that I should deal with this problem?

Thanks,

Jacob.

This will find the number of absence of each student, and first of all will be in accordance with your absence. Select the studentname, count (absences_id) students from LEFT OUTER by a non-SSDNT_ID = a.student_id GROUP count by student name ORDER (absences_id) DESC, student_name

Left OUTER JOIN is important because there may not be any rows in the absence table for some students, in that case, all the fields will be empty due to the absence of that row, and the count will be zero because it is only non - Calculates nil areas.

If there are many dates in each absence, eg. Illness due to sick leave or extended leave, then you add start_date, end_date to the residual table, and change the COUNT (absence_id) with the SUM (DATADFF (day, start_date, end_date), e.g.,

  Student name, SUM (ISNULL (DATEDIFF (day, start_data, expiry date), 0) Join students on the left over on the ASTITENT_ID = A.Student_ID group's student name  

You can order Klaus set Syuem (...) but it will improve the readability and maintenance to wrap it in a query.