I am working on writing a SQL query to produce a table that will look something like this:
Name |Dates Absent|Total Absences student |10/28/2018 | 2 |10/29/2018 |
I currently have a data base which has 2 tables that are part of a larger system which contain the needed data (absences, students).
I have tried the following query
SELECT s.student_id,s.last_name,s.first_name, COUNT(s.student_id) AS 'Total Absences' FROM `students` s, `absences` a INNER JOIN students ON students.student_id=a.student_id
Which yielded the following results:
student_id | last_name | first_name | Total Absences 1 | student | name | 12464
I want this to only use each ID once and count the times it appears. Is the problem from a relationship in the database that has many dates the one person can be absent? The ID was left in the select for now for debugging purposes, it will be removed later.