— from mkacskos1 AT niu.edu (Matt Kacskos) select course.COURSE_ID as “Course ID”, NVL(legacySa.numLegacySa,0) + NVL(sa.numSa,0) AS “Total Number of Classic SA”, NVL(assignment.numAssignments,0) – NVL(sa.numSa,0) AS “Number of Regular Assignments” from (select cm.COURSE_ID from COURSE_MAIN cm ) course left join ( select cm.COURSE_ID, COUNT(*) as numAssignments from COURSE_MAIN cm join COURSE_CONTENTS cc on (cc.CRSMAIN_PK1 = cm.PK1) where cc.CNTHNDLR_HANDLE = ‘resource/x-bb-assignment’ group by cm.COURSE_ID ) assignment on (assignment.COURSE_ID = course.COURSE_ID) left join ( select cm.COURSE_ID, COUNT(*) as numLegacySa from COURSE_MAIN cm join COURSE_CONTENTS cc on (cc.CRSMAIN_PK1 = cm.PK1) where cc.CNTHNDLR_HANDLE = ‘resource/x-mdb-assignment’ group by cm.COURSE_ID ) legacySa on (legacySa.COURSE_ID = course.COURSE_ID) left join ( select cm.COURSE_ID, COUNT(*) as numSa from COURSE_MAIN cm join mdb_safeassign_item msi on (msi.crsmain_pk1 = cm.PK1) group by cm.COURSE_ID ) sa on (sa.COURSE_ID = course.COURSE_ID) where (course.COURSE_ID like ‘1169-%’ or course.COURSE_ID like ‘MASTER-1169%’) order by course.COURSE_ID; ]]>
