General developer forum

Useful SQL Queries?

 
Picture of Jarvis Labrador
SQL Module Attendance Moodle 3.0
 

Hello, thank you for your contributions really helped me a lot to work the reports, today I would like to see the possibility you will help me with the following sql if possible: I need to build a list of the attendance module, where I indicate by group, number of students enrolled, those who were present and absent, and an average percentage of the absent and present, as well as see which group was the most attended and absent,

help me please I leave you the one I have but I know it makes me much more.
SELECT
 Grupo,
 Matriculados,
 Maximo as "Máximo",
 ROUND(AVG(Registradas), 1) as Registros,
 ROUND(AVG(Presentes), 1) as Presentes,
 CONCAT(ROUND(AVG(PresentismoA), 1), "%") as "Presentismo A",
 CONCAT(ROUND(AVG(PresentismoB), 1), "%") as "Presentismo B"
FROM (
  SELECT
     GRP.name as Grupo,
     GRP.id as GrupoId,
     CANT.matriculados as Matriculados,
     MAXI.maximo as Maximo, 
     COUNT(USR.id) as Registradas,
     ROUND(SUM(STS.grade), 0) as Presentes,
     ROUND(SUM(STS.grade)*100/CANT.matriculados, 0) as PresentismoA,
     ROUND(SUM(STS.grade)*100/MAXI.maximo, 0) as PresentismoB
   FROM 
     prefix_role_assignments as ROL
     JOIN prefix_context as CTX on ROL.contextid = CTX.id and CTX.contextlevel = 50
     JOIN prefix_user as USR on USR.id = ROL.userid
     JOIN prefix_course as CRS on CTX.instanceid = CRS.id
     JOIN prefix_groups_members as GM on USR.id = GM.userid
     JOIN prefix_groups as GRP on GM.groupid = GRP.id and GRP.courseid = CRS.id 
     JOIN prefix_attendance_log LOG on LOG.studentid = USR.id
     JOIN prefix_attendance_sessions SES on LOG.sessionid = SES.id and SES.groupid = GRP.id
     JOIN prefix_attendance_statuses STS on LOG.statusid = STS.id and STS.attendanceid = SES.attendanceid AND SES.statusset = STS.setnumber   
     JOIN ( 
        SELECT 
          GM.groupid as id,
          COUNT(USR.id) as "matriculados"
        FROM 
           prefix_role_assignments as ROL
           JOIN prefix_context as CTX on ROL.contextid = CTX.id and CTX.contextlevel = 50
           JOIN prefix_user as USR on USR.id = ROL.userid
           JOIN prefix_course as CRS on CTX.instanceid = CRS.id
           JOIN prefix_groups_members as GM on USR.id = GM.userid
        WHERE 
          ROL.roleid = 5
          AND USR.deleted = 0
          AND CRS.id = 29
        GROUP BY GM.groupid
     ) AS CANT ON CANT.id = GRP.id
     JOIN (
        SELECT
          PRES.groupid AS id,
          ROUND(MAX(PRES.suma), 0) AS "Maximo"
        FROM
        (
          SELECT
             GRP.id as groupid,
             SUM(STS.grade) AS suma
           FROM 
             prefix_role_assignments as ROL
             JOIN prefix_context as CTX on ROL.contextid = CTX.id and CTX.contextlevel = 50
             JOIN prefix_user as USR on USR.id = ROL.userid
             JOIN prefix_course as CRS on CTX.instanceid = CRS.id
             JOIN prefix_groups_members as GM on USR.id = GM.userid
             JOIN prefix_groups as GRP on GM.groupid = GRP.id and GRP.courseid = CRS.id 
             JOIN prefix_attendance_log LOG on LOG.studentid = USR.id
             JOIN prefix_attendance_sessions SES on LOG.sessionid = SES.id and SES.groupid = GRP.id
             JOIN prefix_attendance_statuses STS on LOG.statusid = STS.id and STS.attendanceid = SES.attendanceid AND SES.statusset = STS.setnumber
           WHERE 
             ROL.roleid = 5
             AND USR.deleted = 0
             AND STS.deleted = 0
             and CRS.id = 29
             AND GRP.name > "Comisión 000 (e-ABC)"
             AND GRP.name < "Comisión 353"    
          GROUP BY SES.id
        ) AS PRES
        GROUP BY PRES.groupid
     ) AS MAXI ON MAXI.id = GRP.id
   WHERE 
     ROL.roleid = 5
     AND USR.deleted = 0
     AND STS.deleted = 0
     and CRS.id = 29
     AND GRP.name > "Comisión 000 (e-ABC)"
     AND GRP.name < "Comisión 353"    
  GROUP BY SES.id
) as CLASES
GROUP BY GrupoId
ORDER BY Grupo



 
Average of ratings: -