gb2_queries.sql

# 1 - Total # of gradebooks
select count(*) from GB_GRADEBOOK_T;

# 2 - Total # of categories
select count(*) from GB_CATEGORY_T;

# 3 - Total # of grade items
select count(*) from GB_GRADABLE_OBJECT_T where OBJECT_TYPE_ID = 1 and REMOVED = 0;

# 4 - Total # of users who have created a gradebook
select count(distinct s.CREATEDBY) from GB_GRADEBOOK_T gb join SAKAI_SITE s on gb.GRADEBOOK_UID = s.SITE_ID;

# 5 - Total # of gradebooks per grade type
select GRADE_TYPE,count(*) from GB_GRADEBOOK_T group by GRADE_TYPE;

# 6 - Total # of grade items per grade type
select gb.GRADE_TYPE, count(*) from GB_GRADABLE_OBJECT_T obj join GB_GRADEBOOK_T gb on obj.GRADEBOOK_ID = gb.ID group by gb.GRADE_TYPE;

# 7 - Total # of users per grade type
select GRADE_TYPE,count(distinct s.CREATEDBY) from GB_GRADEBOOK_T gb join SAKAI_SITE s on gb.GRADEBOOK_UID = s.SITE_ID group by GRADE_TYPE;

# 8 - Total # of gradebooks per category type
select CATEGORY_TYPE, count(*) from GB_GRADEBOOK_T group by CATEGORY_TYPE;

# 9 - Total # of grade items per category type
select gb.CATEGORY_TYPE, count(*) from GB_GRADABLE_OBJECT_T obj join GB_GRADEBOOK_T gb on obj.GRADEBOOK_ID = gb.ID group by gb.CATEGORY_TYPE;

# 10 - Total # of users per category type
select gb.CATEGORY_TYPE, count(distinct s.CREATEDBY) from GB_GRADABLE_OBJECT_T obj join GB_GRADEBOOK_T gb on obj.GRADEBOOK_ID = gb.ID join SAKAI_SITE s on gb.GRADEBOOK_UID = s.SITE_ID group by gb.CATEGORY_TYPE;

# 11 - Total # of categories by weight type
select IS_EQUAL_WEIGHT_ASSNS, ENFORCE_POINT_WEIGHTING, count(*) from GB_CATEGORY_T where IS_UNWEIGHTED = 0 group by IS_EQUAL_WEIGHT_ASSNS, ENFORCE_POINT_WEIGHTING;

# 12 - Total # of users by weight type
select cat.IS_EQUAL_WEIGHT_ASSNS, cat.ENFORCE_POINT_WEIGHTING, count(distinct s.CREATEDBY) from GB_CATEGORY_T cat join GB_GRADEBOOK_T gb on cat.GRADEBOOK_ID = gb.ID join SAKAI_SITE s on gb.GRADEBOOK_UID = s.SITE_ID where cat.IS_UNWEIGHTED = 0 group by cat.IS_EQUAL_WEIGHT_ASSNS, cat.ENFORCE_POINT_WEIGHTING;

# 13 - Total # of items treating null as 0
select count(*) from GB_GRADABLE_OBJECT_T where IS_NULL_ZERO = 1;

# 14 - Total # of gradebooks with at least 1 item treating null as 0;
select count(distinct gb.ID) from GB_GRADEBOOK_T gb join GB_GRADABLE_OBJECT_T obj on gb.ID = obj.GRADEBOOK_ID where obj.IS_NULL_ZERO = 1;

# 15 - Total # of users with at least 1 item treating null as 0
select count(distinct s.CREATEDBY) from GB_GRADEBOOK_T gb join GB_GRADABLE_OBJECT_T obj on gb.ID = obj.GRADEBOOK_ID join SAKAI_SITE s on gb.GRADEBOOK_UID = s.SITE_ID where obj.IS_NULL_ZERO = 1;

# 16 - Total # of unreleased items that are counted in the final grade
select count(*) from GB_GRADABLE_OBJECT_T where UNGRADED = 0 and RELEASED = 0;

# 17 - Total # of users with at least 1 unreleased item counted in the final grade
select count(distinct s.CREATEDBY) from GB_GRADABLE_OBJECT_T obj join GB_GRADEBOOK_T gb on obj.GRADEBOOK_ID = gb.ID join SAKAI_SITE s on gb.GRADEBOOK_UID = s.SITE_ID where obj.UNGRADED = 0 and obj.RELEASED = 0;