Gradebook score provider and Content Handler counting (OpenDB)

Version 1

    This query gives a list of all gradebook score providers (and their item counts) for all courses that match the pattern '%-20168'

    SELECT sp.name, gm.score_provider_handle, count(gm.pk1)
    FROM bb_bb60.course_main cm
    INNER JOIN bb_bb60.gradebook_main gm ON cm.pk1 = gm.crsmain_pk1
    LEFT JOIN bb_bb60.score_provider sp ON gm.score_provider_handle = sp.handle
    WHERE cm.course_id LIKE '%-20168' 
    GROUP BY sp.name, gm.score_provider_handle
    ORDER BY count(gm.pk1) DESC;
    
    

     

    This query gives a list of all content handlers (and their item counts) for all courses that match the pattern '%-20168'

    SELECT ch.name, cc.cnthndlr_handle, count(cc.pk1)
    FROM bb_bb60.course_main cm
    INNER JOIN bb_bb60.course_contents cc ON cm.pk1 = cc.crsmain_pk1
    LEFT JOIN bb_bb60.content_handlers ch ON cc.cnthndlr_handle = ch.handle
    WHERE cm.course_id LIKE '%-20168'
    GROUP BY ch.name, cc.cnthndlr_handle
    ORDER BY count(cc.pk1) DESC;
    
    

     

    In both queries, you will want to change '%-20168' to match whatever pattern you'd like to match against for the course_id