General developer forum

Hello guys,

here is the answer..Thanks to 'Emmanuel Bakare' for a  awesome query.. I have added some extra stuff to this query according to the my requirements...

SELECT u.id, u.username,  c.id courseID, c.fullname CourseName
,
   
        IFNULL((SELECT COUNT(gg.finalgrade)
          FROM mdl_grade_grades AS gg
          JOIN mdl_grade_items AS gi ON gg.itemid=gi.id
          WHERE gi.courseid=c.id
           AND gg.userid=u.id
           AND gi.itemtype='mod'
           GROUP BY u.id,c.id),'0') AS 'Activities Completed'
,
 
        IFNULL((SELECT COUNT(gi.itemname)
          FROM mdl_grade_items AS gi
          WHERE gi.courseid = c.id
           AND gi.itemtype='mod'), '0') AS 'Activities Assigned'
,
 
/*If Activities completed = activities assigned, show date of last log entry. Otherwise, show percentage complete. If Activities Assigned = 0, show 'n/a'.--*/
(SELECT IF(`Activities Assigned`!='0', (SELECT IF((`Activities Completed`)=(`Activities Assigned`),
/*--Last log entry--*/
(SELECT CONCAT('100% completed ',FROM_UNIXTIME(MAX(log.TIME),'%m/%d/%Y'))
FROM mdl_log log
WHERE log.course=c.id
AND log.userid=u.id),
/*--Percent completed--*/
(SELECT CONCAT(IFNULL(ROUND((`Activities Completed`)/(`Activities Assigned`)*100,0), '0'),'% complete')))), 'n/a')) AS '% of Course Completed'


FROM mdl_user u

INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id

INNER JOIN mdl_enrol e ON e.id = ue.enrolid

INNER JOIN mdl_course c ON e.courseid = c.id

INNER JOIN mdl_course_modules cm ON c.id=cm.course

INNER JOIN mdl_modules module ON module.id=cm.module

INNER JOIN mdl_course_sections cs ON cs.id=cm.section
WHERE u.id='$userid'
 

 AND cs.section !=0   /* Remove this portion if you want to also list the general activities on top of a course */

 GROUP BY c.fullname