Moodle 4.3 SCORM数据：按cmd.interactions_[nr].id统计student_response值数量

在Moodle 4.3版本中，SCORM数据被拆分存储至3个数据库表。想确认是否可以针对对应的cmd.interactions_[nr].id名称，统计student_response的value值的出现数量？

目前已通过表自连接实现部分效果，示例查询结果如下：

现有SQL查询代码如下：

SELECT 
    concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>')  as course,
    sca.scormid,
    sc.name ,
     sca.attempt,
    scelem.element,
    slide.value as "Question",
 
    @answer:=( 
        SELECT scval.value 
        FROM prefix_scorm_scoes_value AS scval 
            JOIN prefix_scorm_element AS scelem2 ON scelem2.id = scval.elementid
        WHERE scval.scoid = slide.scoid 
            AND scval.attemptid = slide.attemptid
            AND scelem2.element = concat (LEFT(scelem.element, LENGTH(scelem.element)-3) , '.student_response') 
   ) AS "resultvalue" ,
   @answer:=( 
        SELECT scval.value 
        FROM prefix_scorm_scoes_value AS scval 
            JOIN prefix_scorm_element AS scelem2 ON scelem2.id = scval.elementid
        WHERE scval.scoid = slide.scoid 
            AND scval.attemptid = slide.attemptid
            AND scelem2.element = concat (LEFT(scelem.element, LENGTH(scelem.element)-3) , '.pattern') 
   ) AS "pattern" 
   
 
   
FROM prefix_scorm AS sc
JOIN prefix_scorm_attempt As sca ON sca.scormid=sc.id
JOIN prefix_scorm_scoes_value as slide ON slide.attemptid = sca.id
JOIN prefix_scorm_element as scelem on scelem.id = slide.elementid
JOIN prefix_course AS c ON c.id = sc.course
JOIN prefix_course_categories AS cats ON c.category = cats.id

        

WHERE 
    sc.course = 158 AND sc.name ="XY"

AND  NOT scelem.element LIKE '%cmi.suspend_data%' 
AND NOT scelem.element LIKE '%objectives%' 
 AND (scelem.element LIKE '%.id%' OR scelem.element LIKE '%.pattern%'    )


 GROUP BY       SUBSTRING_INDEX(slide.value, '_', 3)
ORDER BY  sca.attempt, slide.elementid

内容的提问来源于stack exchange，提问作者Jay