CODEWITHSUNDEEP
mysqlsqldatabase
Enter Strandman
Enter Strandman

Reputation: 339

SQL Query, Getting multiple dates for one entity

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.

EDIT

I now have the query 

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

This only displays one of the dates, how I can add all of the dates without redisplaying the students information?

Upvotes: 0

Views: 392

Answers (2)

Nick Ellis
Nick Ellis

Reputation: 1077

You can do this with group_concat. It's not quite what you descibe, but it's close.

SELECT s.student_id,s.last_name,s.first_name, group_concat(a.date_absent) AS 'Dates Absent', COUNT(a.id) AS 'Total Absences' 
FROM `students` s JOIN `absences` a ON s.student_id = a.student_id
GROUP BY s.student_id

which should yield

student_id | last_name | first_name | Dates Absent          | Total Absences
1          |   student |       name | 10/28/2018,10/29/2018 | 2

Upvotes: 4

Arth
Arth

Reputation: 13110

It looks like you are almost there with the counting, but missing your GROUP BY statement

If you include aggregate functions, such as COUNT(), but leave off the GROUP BY, the whole intermediate result is taken as one group

You also seem to have a strange CROSS JOIN going on with your duplicate mention of the students table

If you want the absence dates in each row you'll have to use another aggregate function, GROUP_CONCAT()

Something along the lines of

   SELECT s.student_id, /** Include as names could feasibly be duplicated */ 
          CONCAT(s.first_name, ' ', s.last_name) name, 
          GROUP_CONCAT([DISTINCT] a.date) dates_absent, /** May want distinct here if more than one absence is possible per diem */
          COUNT(*) total_absences 
     FROM students s
     JOIN absences a
       ON a.student_id = s.student_id
 GROUP BY s.student_id[, name] /** name required for SQL standard */
[ORDER BY name [ASC]] /** You'll probably want some kind of ordering */

[] indicate optional inclusions

Upvotes: 1

Related Questions