Analytics and reporting

Ad-hoc database queries help

Picture of Marcus Wynwood
Ad-hoc database queries help

Hi everyone,

I'm working on a query to be run with the "Ad-hoc database queries" add on.

We have students in groups and groupings in a course, and I'm after a report to display the following data for a single given course (eg: courseid = 2)


| name         | email             | group        | grouping         | completed  |
| Fred Smith   |  | Monday Class | Weekday Students | 2013-09-18 |
| Barry Porter |  | Friday Class | Weekday Students |            |
| Sally Jones  | | Sunday Class | Weekend Students | 2013-03-23 |

The name and email come from the mdl_user table.

The group can be worked out based on the course ID (there is only one course)

The grouping should be able to be worked out based on the groupid

The completed is from the timecompleted field in the mdl_course_completions table based on the userid and the courseid.


If anyone could help out, that would be amazing!

Thanks!! smile

Average of ratings: -
Randy Thornton
Re: Ad-hoc database queries help
Documentation writersParticularly helpful Moodlers

Try this:

SELECT DISTINCT concat(u.firstname, ' ',u.lastname) AS name,, AS groupname, AS Grouping, DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
),'%Y-%m-%d') AS Completed
FROM prefix_course AS c
JOIN prefix_groups AS g
JOIN prefix_groups_members AS m ON = m.groupid
JOIN prefix_user AS u ON m.userid =
JOIN prefix_groupings_groups AS gg ON = gg.groupid
JOIN prefix_groupings AS gr ON gg.groupingid =
JOIN prefix_course_completions AS p ON p.course =
WHERE = 3 AND p.userid =
ORDER BY u.firstname

I think that will work. Let me know if it does. Sample of data I get is below. Seems to work okay. In the example there are four groups in two groupings ( = 3).

I snagged most of this from queries I did a while ago for groups and completion that I put over in the 2.5 ad hoc queries area. Just had to figure out the groups/groupings link.


Average of ratings: Useful (3)
Picture of Marcus Wynwood
Re: Ad-hoc database queries help

Thanks so much Randy! Works great big grin

Average of ratings: -
Απάντηση: Re: Ad-hoc database queries help

I need a query to count the number of students and Non-editing teachers who are enrolled per course. The final report must have Country, course's name, Number of students, Number of Non editing teachers. That's all. I have no SQL experience. Kindly request for help.

Average of ratings: -