SQL LOGIC using 3conditions

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 the count of each user has Completed modules, Inprocess modules and Notstarted modules for each course, where getting the count of userids from table CC by taking courseia from table CM, get number of modules that an user has completed from each course.

(A course can have morethan one module and a course can have number of users attempted all modules, few modules or not attempted at all).

So, I need number of users - has done number of modules - in a course. (3 logics)

Completed.Users means : If number of modules attempted is equal to number of modinstance from table CMS (ex: no. of modules attempted by a user per course= 9, no.modinstance = 9. Because 7 is not equal to 9, They are completed.)

Inprocess.Users means : Number of modules attempted should be >0, but not equal to [count(modinstance) per course] (ex: no. of modules attempted by a user per course= 7 , no.modinstance = 9. Because 7 is not equal to 9, They are Inprocess.)

Notstarted.Users means : Number of modules attempted should be equal to 0, (ex: no. of modules attempted by a user per course= 0. They are Notstarted).

    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 was trying, I couldn't get a solution. I hope stackoverflow developers could help me out. I tried with my query :

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

via Chebli Mohamed