SQL Query, Getting multiple dates for one entity

Question

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?

a better question title would help other users in the future; the current one is too vague... — Rubens Farias
– Rubens Farias, Commented Nov 5, 2018 at 17:59
You'll need group_concat() to get the exact results you want, though often using group_concat() is a sign you're doing work on the DB you should really move the client application. — Joel Coehoorn
– Joel Coehoorn, Commented Nov 5, 2018 at 18:00
Could you explain why you changed the accepted answer? I have no problem with the accepted answer (which is now very similar to mine and I upvoted it), but that rep loss hurts! — Arth
– Arth, Commented Nov 6, 2018 at 15:41
The other answer had the wrong results, the number of days missed was in the hundreds and it should've been a single digit number — Enter Strandman
– Enter Strandman, Commented Nov 6, 2018 at 15:56

Nick Ellis · Accepted Answer · 2018-11-05 18:35:57Z

4

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

edited Nov 5, 2018 at 18:35

answered Nov 5, 2018 at 18:02

Nick Ellis

1,07711 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arth Over a year ago

looks like there's still a strange cross join going on in your solution which may affect the counts

Nick Ellis Over a year ago

I'm not sure what you mean. Students has many absences, you connect to them and group by student_id. I don't see any potential for a cross join issue. There could be a problem if there are multiple absences per day.

Arth Over a year ago

FROM students s, absences a INNER JOIN students .... This will cross join the tables students and absences (creating a row for every student and absence combination), then INNER JOIN the whole lot with students again. I can't imagine this is what OP wanted.

Nick Ellis Over a year ago

You are correct. I didn't realize when I copied from the original query that the students table was included twice. FIXED.

Arth · Accepted Answer · 2018-11-05 18:12:06Z

1

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

edited Nov 5, 2018 at 18:12

answered Nov 5, 2018 at 18:06

Arth

13.1k5 gold badges43 silver badges72 bronze badges

1 Comment

Arth Over a year ago

@EnterStrandman, your edit appeared to be only removing the square brackets which I included only as indications of optional inclusions (after removing the square brackets). This is standard syntax in the MySQL docs. The comments are of course optional as well.

Collectives™ on Stack Overflow

SQL Query, Getting multiple dates for one entity

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related