moodle query

moodle query

by Himanshi Khurana -
Number of replies: 0

i tried to combine these 2 queries..

query 1:


SELECT TRIM(c.id) course_id
    ,TRIM(c.fullname) AS course_fullname
    ,FROM_UNIXTIME(c.startdate) startdate
    ,c.summary
    ,COUNT(ra.id) AS enrol_count
FROM mdl_course c
JOIN mdl_context ct
    ON (ct.INSTANCEID = c.ID)
LEFT JOIN mdl_role_assignments ra
    ON (ra.CONTEXTID = ct.ID)
WHERE ct.CONTEXTLEVEL = 50
    AND ra.ROLEID = 5
GROUP BY c.id



query 2:

SELECT mdl_course.id as course_id , FROM_UNIXTIME(MAX(ue.timecreated)) AS latest_enrolment_date,mdl_course.fullname

FROM mdl_enrol e

JOIN mdl_user_enrolments ue 

ON ue.enrolid = e.id

join mdl_course 

on mdl_course.id=e.courseid 

group by e.courseid
resultant query after joining above two queries:

SELECT TRIM(c.id) course_id,TRIM(c.fullname) as course_fullname,FROM_UNIXTIME(c.startdate) startdate ,c.summary,COUNT(ra.id) AS enrol_count
FROM  mdl_course c 
JOIN mdl_context ct 
ON ( ct.INSTANCEID = c.ID ) 
LEFT JOIN mdl_role_assignments ra 
ON ( ra.CONTEXTID = ct.ID ) 
join mdl_enrol on mdl_enrol.courseid=c.id
join mdl_user_enrolments ue 
ON ue.enrolid = mdl_enrol.id   
WHERE  ct.CONTEXTLEVEL = 50 AND ra.ROLEID = 5 
GROUP BY c.id


if i try to combine these two queries using the following query, value of enrol_count goes wrong...

Average of ratings: -