Moodle in English

Hi,

We are using the ad-hoc reports.

We need to know the number of users how uses Moodle on a yearly bases.

We found one query that gives monthly but we need yearly one.

This the one available:

Monthly Usage by Role

This report shows a distinct count of users by their role, accessing your site. Each instance of user and role is counted once per month, no matter how many courses they access. We use this to show the total number of students and teachers accessing our site.

SELECT 
MONTH(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_month,
YEAR(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_year,
prefix_role.name AS user_role,
COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users
FROM
prefix_stats_user_monthly
INNER JOIN prefix_role_assignments ON prefix_stats_user_monthly.userid = prefix_role_assignments.userid
INNER JOIN prefix_context ON prefix_role_assignments.contextid = prefix_context.id
INNER JOIN prefix_role ON prefix_role_assignments.roleid = prefix_role.id
WHERE prefix_context.contextlevel = 50
AND `prefix_stats_user_monthly`.`stattype` = 'activity'
AND prefix_stats_user_monthly.courseid <>1
GROUP BY MONTH(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),
YEAR(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),
prefix_stats_user_monthly.stattype,
prefix_role.name
ORDER BY 
YEAR(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), MONTH(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),
prefix_role.name

We need the same result but on a yearly bases to know exactly how many users where using the site every year.

Thank you indeed.

Salim