MySQL SUM with CASE choose latest record by date

Question

I've got the SUM CASE statements working properly. The issue is that I have multiple records with similar criteria, so I'd like to select the latest record by date.

 SELECT 
    SUM(CASE WHEN planning like 'Rotation%' THEN 1 ELSE 0 END + 
        CASE WHEN assessmentanddata like 'Collects%' THEN 1 else 0 END +
        CASE WHEN path like 'Same%' THEN 1 else 0 END +
        CASE WHEN place like 'Move%' THEN 1 else 0 END +
        CASE WHEN pace like 'Timer%' THEN 1 else 0 END +
        CASE WHEN classroommanagement like 'Restating%' THEN 1 else 0 END +
        CASE WHEN teacherrole like 'Mini%' THEN 1 else 0 END +
        CASE WHEN studentengagement like 'Follow%' THEN 1 else 0 END +
        CASE WHEN studentcollaboration like 'Collects%' THEN 1 else 0 END +
        CASE WHEN technology like 'Technology%' THEN 1 else 0 END)  AS p1
    from ruberic where schoolId = 1

A sample from the table will be these 3 columns of DATE, SCHOOLID, and TEACHERID:

2016-12-05 1 1 -> This record will be fine

2016-12-05 1 4 -> Select only this when compared with the record below

2016-12-05 1 4

Perhaps the down votes are due to an unfinished question. There is no `FROM` or `WHERE` clause. Based on my understanding of the preface, the `CASE` is mostly a distraction. See, e.g., http://stackoverflow.com/questions/8523374/mysql-get-most-recent-record — alttag, Jan 05 '17 at 19:52
First post, worried about the editing, missed the 'from'/'where'. — JWK, Jan 05 '17 at 19:55
Work on it piece by piece. If your starting query works, then go on to add "when schoolId and teacherId are the same" to your `WHERE` clause. Once you have that done, check the (many) other questions on SO about grabbing the most recent record. — alttag, Jan 05 '17 at 19:59
If you wish to clarify, please edit the question. That's a mess in the comments. — alttag, Jan 05 '17 at 20:07
See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jan 05 '17 at 21:47

score 0 · Accepted Answer · edited Jun 04 '17 at 20:59

Building on what I see (I don't see the field name for date so assumed date)

This uses a correlates subquery and an exists statement to identify the max(date) for each teacherID and school and then limits the main dataset by this subset through the coloration.

SELECT 
    SUM(CASE WHEN planning like 'Rotation%' THEN 1 ELSE 0 END + 
        CASE WHEN assessmentanddata like 'Collects%' THEN 1 else 0 END +
        CASE WHEN path like 'Same%' THEN 1 else 0 END +
        CASE WHEN place like 'Move%' THEN 1 else 0 END +
        CASE WHEN pace like 'Timer%' THEN 1 else 0 END +
        CASE WHEN classroommanagement like 'Restating%' THEN 1 else 0 END +
        CASE WHEN teacherrole like 'Mini%' THEN 1 else 0 END +
        CASE WHEN studentengagement like 'Follow%' THEN 1 else 0 END +
        CASE WHEN studentcollaboration like 'Collects%' THEN 1 else 0 END +
        CASE WHEN technology like 'Technology%' THEN 1 else 0 END)  AS p1
FROM
    ruberic R1
WHERE
    schoolId = 1 
    AND EXISTS (SELECT Null 
                FROM ruberic r2
                WHERE R2.SchoolID = R1.SchoolID
                  AND R2.TeacherID = R1.TeacherID
                GROUP BY SchoolID, TeacherID
                HAVING R1.Date = MAX(R2.Date) )

MySQL SUM with CASE choose latest record by date

1 Answers1