General developer forum

Hi,

SELECT t1.id
,t1.shortname
,t2.nbtotal
,t3.nbcompleted
,t4.nbstarted
,t5.nbnotstarted
FROM mdl_course t1
LEFT JOIN (SELECT course, count(userid) nbcompleted FROM mdl_course_completions WHERE timecompleted is not null GROUP BY course) t3 ON t3.course = t1.id
LEFT JOIN (SELECT course, count(userid) nbstarted FROM mdl_course_completions WHERE timestarted > 0 AND timecompleted is null GROUP BY course) t4 ON t4.course = t1.id
LEFT JOIN (SELECT course, count(userid) nbnotstarted FROM mdl_course_completions WHERE timestarted = 0 AND timecompleted is null GROUP BY course) t5 ON t5.course = t1.id
,(SELECT course, count(userid) nbtotal FROM mdl_course_completions GROUP BY course) t2
WHERE t2.course = t1.id
ORDER BY t1.id asc

I hope it's helped or at least that was a good start !

Have a nice day.

Dominique.