Using a little SQL to count the number of assignments in our moodle courses...

My customer emailed me this today
"

James,

 

If you don't have this information easily accessible, don't bother.

 

I'm looking for a report that would show how many assignments are in each course's User Report.  It's for the purpose of creating pacing charts in Genius. 

 

If you don't have it, don't bother taking up any time.  We can get it by going to the courses.

 

Thanks."

 

This sounded familiar in my brain.  So I checked loaded up MySQL Workbench, where I have lots of little snippets of SQL to answer questions like this.

 

I found this apply titled snippet Count of assignments in courses. Promising.

 

Contents of snippet:

 

"

SELECT  m.course, c.fullname as coursename, m.name as assignment_name, x.count_of_assignments
       FROM mdl_assignment m
      
       inner join (
          select  course, count(*) as count_of_assignments
        from mdl_assignment
          group by course
          ) x
         
          on m.course = x.course
         
          join mdl_course c on c.id = m.course
         
          ORDER by coursename
"

 

This nice little piece of SQL uses an nested select statement to get the count and then passes that back to the first select.

Using MySQL workbench, I viewed the result and sent it to a CSV file.

Send the .csv file to my customer.  Total time spend on task, 90 seconds.

That's a little ROI, return on investment - and is why solving problems - gleening answers to questions and SAVING THEM AS SNIPPETS is simply a must.

Many moons ago, I said it this way " a programmer has a toolkit of little programming code(s) - where he can apply them to various problems - avoiding the recreation of the wheel."