Databases: Counting Students inside each course and courses inside each category or subcategory

Databases: Counting Students inside each course and courses inside each category or subcategory

by Ângelo Rigo -
Number of replies: 6

Hi

I want to discover how many students are inside each course, and how many courses are inside each category or subcategory.

Is there a plug-in that can do this task ? If not what are the tables i must investigate? to discover widh query i must write to tell me how many students are inside each course and how many courses are inside each subcategory and category .

Thank´s in advance 

Average of ratings: -
In reply to Ângelo Rigo

Re: Databases: Counting Students inside each course and courses inside each category or subcategory

by Ângelo Rigo -
How can i count just students or users to courses and courses assigned to categorys?
Average of ratings: -
In reply to Ângelo Rigo

Re: Databases: Counting Students inside each course and courses inside each category or subcategory

by Guido Vega -
Hi Ângelo,

I hope I understood your question correctly and find the following helpful (if not just let me know I’ll have another go):

- To check how many courses are inside each subcategory and category you can do 3 things:
  1. As admin you can go Courses -> Add/edit courses and the list of categories got the “Courses” column which is a count of courses in the category
  2. You can check the coursecount column in the mdl_course_categories table
  3. (My favorite option) run the following SQL:
SELECT cc.id, cc.name, COUNT(c.id) AS courses FROM mdl_course_categories cc LEFT OUTER JOIN mdl_course c ON cc.id = c.category GROUP BY cc.id, cc.name order by cc.name;

- Now to check the number of students is a bit tricky and I hope I got it right (if someone with more experience could check it will be great!), try this SQL:

SELECT c.id, c.shortname, COUNT(ra.userid) AS students FROM mdl_course c LEFT OUTER JOIN mdl_context cx ON c.id = cx.instanceid LEFT OUTER JOIN mdl_role_assignments ra ON cx.id = ra.contextid AND ra.roleid = '5' WHERE cx.contextlevel = '50' GROUP BY c.id, c.shortname;

I hope that helped

Regards,
GV

Average of ratings: -
In reply to Guido Vega

Re: Databases: Counting Students inside each course and courses inside each category or subcategory

by Ângelo Rigo -

Thank´s that´s is just what i am looking for.

I understand that the first query is a join with mdl_course_categories and mdl_courses.

And the second query relate mdl_course with mdl_context, and mdl_role_assignments, what does contextlevel = '50' means.

I am learnig about the moodle relational model. Where i can find more useful resources about, or what are the most interesting querys to research the moodle database ?

Best regards. 

Average of ratings: -
In reply to Ângelo Rigo

Re: Databases: Counting Students inside each course and courses inside each category or subcategory

by Guido Vega -
The contextlevel = '50' condition guaranties that we are looking a course. The context definitions are hard-coded in to a file (lib/accesslib) which makes it just a bit harder to trace.

GV

Average of ratings: -
In reply to Guido Vega

Re: Databases: Counting Students inside each course and courses inside each category or subcategory

by Pete Richardson -

Guido,
The information you provided has also helped me work out what's going on with v1.8.2 courses and users.

Thanks very much.

Pete

Average of ratings: -
In reply to Pete Richardson

Re: Databases: Counting Students inside each course and courses inside each category or subcategory

by Guido Vega -
I’m glad that helped wink
Average of ratings: -