Geek Answers Handbook

SQL LOGIC using 3 conditions

course_completions CC id coursemodid userid state timemodified 370 23 2 1 1433582890 329 24 89 1 1427771915 333 30 39 1 1428309816 332 32 39 1 1428303307 327 33 40 1 1427689703 328 34 89 1 1427710711 303 35 41 1 1410258482 358 36 99 1 1432020067 365 25 2 1 1433142455 304 26 69 1 1410717866 353 37 95 1 1430387005 416 38 2 1 1438972465 300 27 70 1 1409824001 302 29 74 1 1412055704 297 30 2 1 1409582123 301 133 41 1 1410255923 336 133 91 1 1428398435 364 133 40 1 1433142348 312 133 85 1 1425863621 course_modules CM id course 23 6 24 6 25 6 26 6 27 6 28 6 29 8 30 8 31 8 32 8 33 8 34 5 35 5 36 5 37 5 38 5 39 9 40 9 41 9 course_mod_settings CMS id course modinstance 27 8 30 28 8 31 29 8 32 30 8 33 31 6 23 32 6 24 33 6 25 34 6 26 35 6 27 36 6 28 37 9 39 38 9 40 39 9 41

I need each user to have completed modules, Inprocess modules, and Notstarted modules for each course, where the number of user identifiers from the CC table is obtained, taking a course from the CM table, getting the number of modules that the user completed from each course.

(A course can have more than one module, and a course can have the number of users who tried to execute all modules, several modules, or did not try at all).

So, I need the number of users - made the number of modules - in the know. (3 logics)

Completed.Users means : if the number of module attempts is equal to the number of modinstance from the CMS table (for example: the number of modules taken by the user on the course = 9, no.modinstance = 9 Since 7 is not equal to 9, they are completed.)

Inprocess.Users means : the number of module attempts must be> 0, but not equal to [count (modinstance) per course] (for example: the number of modules taken by the user on the course = 7, no.modinstance = 9. Since 7 is not equal to 9, they are inprocess.)

Notstarted.Users means . The number of module attempts should be 0 (for example: the number of modules that the user tried to complete on the course = 0. They do not start).

  OUTPUT : Course No.Completed.Users No.Inprocess.Users No.Notstarted.Users 5 65 32 6 6 40 12 15 8 43 56 0 9 0 7 9

Sir, this is a very critical logic that I tried, I could not find a solution. I hope stackoverflow developers can help me. I tried with my request:

 SELECT cm.course AS "Course", (CASE WHEN (SELECT count(cms.id) FROM course_mod_settings cms) = (SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) THEN COUNT(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) END) AS "No.Completed.Users", (CASE WHEN (SELECT count(cms.id) FROM course_mod_settings cms) > 0 AND (SELECT count(cms.id) FROM course_mod_settings cms) != (SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) THEN COUNT(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) END) AS "No.Inprocess.Users", (CASE WHEN (SELECT count(cms.id) FROM course_mod_settings cms) = 0 THEN COUNT(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) END) AS "No.Notstarted.Users" FROM mdl_course c GROUP BY c.id
+4
source share
1 answer

SQL Fiddle

 SELECT course AS "Course", SUM(CASE WHEN completion_count = module_count THEN 1 ELSE 0 END) AS "No.Completed.Users", SUM(CASE WHEN completion_count > 0 AND completion_count < module_count THEN 1 ELSE 0 END) AS "No.Inprocess.Users", SUM(CASE WHEN completion_count = 0 THEN 1 ELSE 0 END) AS "No.Notstarted.Users" FROM (SELECT course, COUNT(*) AS module_count FROM course_modules cm GROUP BY course) course_module_counts JOIN (SELECT cm.course AS courseid, users.id AS userid, SUM(CASE WHEN cc.state = 1 THEN 1 ELSE 0 END) completion_count FROM ((SELECT DISTINCT userid AS id FROM course_completions) users CROSS JOIN course_modules cm) LEFT JOIN course_completions cc ON users.id = cc.userid AND cc.coursemodid = cm.id GROUP BY cm.course, users.id) course_completion_counts ON course_module_counts.course = course_completion_counts.courseid GROUP BY course

gives this result, which corresponds to the limited data set that you indicated in your question.

 | course | No.Completed.Users | No.Inprocess.Users | No.Notstarted.Users | |--------|--------------------|--------------------|---------------------| | 5 | 0 | 5 | 7 | | 6 | 0 | 4 | 8 | | 8 | 0 | 4 | 8 | | 9 | 0 | 0 | 12 |
+1
source

More articles:


All Articles