SQL Query Needs Help,

751 views mysql
0

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.

answered question

a better question title would help other users in the future; the current one is too vague...

1 Answer

11

You need to use "group by" statement like:

SELECT s.student_id, COUNT(s.*) AS 'Total Absences' 
FROM `students` s,  
INNER JOIN `absences` a ON s.student_id=a.student_id
GROUP BY s.student_ID

posted this

Have an answer?

JD

Please login first before posting an answer.