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