SQL query for Moodle SCORM Question Report in Databasetable scorm_scoes_track

Example: 1 user has completed 2 test attempts. The test questions are taken randomly and therefore different questions are stored in the columns element and value.

How can I count the number of results in column "value" for all occurring questions in same column for the value "SlideX_QuestionY"?

Desired result would be:

   SELECT
    st.* 
    FROM prefix_scorm_scoes_track    AS st
       JOIN prefix_user              AS u   ON st.userid  = u.id
       JOIN prefix_scorm             AS sc  ON sc.id      = st.scormid
       JOIN prefix_course            AS c   ON c.id       = sc.course
    WHERE   sc.course = 684 and st.scormid = 1195  and st.userid = 9780

Something like this

This includes a join on itself because of the way the scorm values are stored. The join includes a substring which will be slow.

There can also be multiple attempts, so this checks for the last attempt

SELECT c.id AS courseid, c.fullname AS coursename,
    s.id AS scormid, s.name AS scormname,
    slide.element,
    slide.value AS slidename,
    SUM(CASE WHEN results.value = 'wrong' THEN 1 ELSE 0 END) AS wrong,
    SUM(CASE WHEN results.value = 'correct' THEN 1 ELSE 0 END) AS correct,
    /* results can have other values so this is just in case */
    SUM(CASE WHEN results.value NOT IN ('correct', 'wrong') THEN 1 ELSE 0 END) AS other
FROM mdl_scorm_scoes_track slide
JOIN mdl_scorm s ON s.id = slide.scormid
JOIN mdl_course c ON c.id = s.course
JOIN mdl_scorm_scoes_track results ON results.userid = slide.userid
    AND results.scormid = slide.scormid
    AND results.scoid = slide.scoid
    AND results.attempt = slide.attempt
    /* A join on a substring will be slow */
    AND results.element = LEFT(slide.element, LENGTH(slide.element)-3) || '.result'
WHERE slide.element LIKE 'cmi.interactions%.id'
AND slide.value LIKE 'Slide%_Question%'
AND EXISTS (
    /* There can be multiple attempts so use the latest attempt only */
    SELECT 1
    FROM mdl_scorm_scoes_track lastattempt
    WHERE lastattempt.userid = slide.userid
    AND lastattempt.scormid = slide.scormid
    HAVING MAX(lastattempt.attempt) = slide.attempt
)
GROUP BY c.id, c.fullname,
   s.id, s.name,
   slide.element,
   slide.value
ORDER BY c.id, c.fullname,
   s.id, s.name,
   slide.element,
   slide.value