postgresql - How to combine in one sql query in extra column the result of 2 group by queries? -

considering following mdl_course_completions table describes course completion user:

id,bigint userid,bigint course,bigint timeenrolled,bigint timestarted,bigint timecompleted,bigint reaggregate,bigint 

to determinate if course has been finished student, use predicate on timecompleted field.

when field null, student has not finished course, when field not null, means student has finished course.

thus, count of number of students finished course course given by:

select mdl_course.fullname,count(*) "number of students didn't finish courses" mdl_course_completions   inner join mdl_course on  mdl_course.id = mdl_course_completions.course timecompleted not null group mdl_course.fullname ; 

the result is:

| course name | number of students finish courses | |-------------|----------------------------------------| | course 1    | 50                                     | | course 2    | 200                                    | | course 3    | 120                                    | 

and count of number of students didn't finished course course given by:

select mdl_course.fullname,count(*) "number student didn't finish courses" mdl_course_completions   inner join mdl_course on  mdl_course.id = mdl_course_completions.course timecompleted null group mdl_course.fullname ; 

the result is:

| course name | number of students didn't finish courses | |-------------|-----------------------------------------------| | course 1    | 12                                            | | course 2    | 12                                            | | course 3    | 120                                           | 

i wonder how can combine 2 queries in 1 query results in column such as:

| course name | number of students finish courses | number of students didn't finish courses | |-------------|------------------------------------|-------------------------------------------| | course 1    | 50                                 | 12                                        | | course 2    | 200                                | 12                                        | | course 3    | 120                                | 120                                       | 

i using postgresql.in opinion, kind of stuff not related database system. don't know how proceed combine these 2 queries in 1 in column group clause.

use conditional aggregation.

select mdl_course.fullname ,sum((timecompleted not null)::int) "number student finish courses" ,sum((timecompleted null)::int) "number student didn't finish courses" mdl_course_completions inner join mdl_course on  mdl_course.id = mdl_course_completions.course  group mdl_course.fullname