mySQL multi-join with distinct count

Question

I am trying to query 3 tables:

classroom:

id |    name  
----------------
 1 | bob's class

student_enrollment:

studentId | classroomId  
-----------------------
     1    |     1

student_progress:

studentId | dateTime 
---------------------   
    1     | 2014-08-15 09:33:23

and I am trying to end up with a result such as:

classroomName | studentId | days
------------------------------------------
Bob's class   |     1     |   112

Where days is the distinct number of days the particular student had a dateTime entry.

SELECT classroom.name, student_enrollment.studentId, student_progress.dateTime   
FROM classroom 
JOIN student_enrollment ON student_enrollment.classroomId = classroom.id     
JOIN student_progress ON student_progress.studentId = student_enrollment.studentId 
WHERE `dateTime` >= '2014-08-01 09:00:34' 
ORDER BY classroom.name

Gives me a table with every dateTime entry for each student. Any way I've tried to add a count to the query gives me at best a result with just one student and the total count of distinct dateTime days.

SELECT classroom.name, student_enrollment.studentId, COUNT(DISTINCT YEAR(student_progress.dateTime),MONTH(student_progress.dateTime),DAY(student_progress.dateTime)) AS days 
FROM classroom 
    JOIN student_enrollment ON student_enrollment.classroomId = classroom.id 
    JOIN student_progress ON student_progress.studentId = student_enrollment.studentId 
WHERE  `dateTime` >= '2014-08-01 09:00:34' 
ORDER BY classroom.name

I've tried moving the count to a sub-query in the join portion, and a variety of other things, but just end up with errors such as unknown table student_progress and just can't seem to land on the right way to put the query together to get the distinct count of dateTime entries for each studentId.

Is the complication that you want to consider days and not just unique dateTime values? — Madbreaks
The complication was that I was just getting back a result with 1 student in 1 classroom with 'days' as a count of ALL distinct dateTime days, rather than the distinct count of days for each particular student. I guess because I had student_enrollment.studentId rather than student_progress.studentId in the SELECT. — soisystems

JNevill JNevill · Accepted Answer · 2015-04-06T19:18:58

It seems like what you want is to count the distinct DATEs. Try, instead:

SELECT classroom.NAME,
    student_enrollment.studentId,
    COUNT(DISTINCT DATE(student_progress.DATETIME)) AS days
FROM classroom
INNER JOIN student_enrollment
    ON student_enrollment.classroomId = classroom.id
INNER JOIN student_progress
    ON student_progress.studentId = student_enrollment.studentId
WHERE `dateTime` >= '2014-08-01 09:00:34'
GROUP BY classroom.NAME, student_enrollment.studentId,
ORDER BY classroom.NAME

I added in the explicit GROUP BY here too, just because anytime you are aggregating with a formula up top, you should GROUP BY the other fields. MySQL will do it anyway, but it's best to be explicit so you don't get strange results.

mySQL multi-join with distinct count

2 Answers