ad-hoc contributed reports: Difference between revisions
Line 99: | Line 99: | ||
GROUP BY grptimed,grptimeh | GROUP BY grptimed,grptimeh | ||
ORDER BY grptimed,grptimeh | ORDER BY grptimed,grptimeh | ||
</code> | |||
===FORUM use Count per COURSE -- not including NEWS Forum!=== | |||
<code sql> | |||
SELECT prefix_course.fullname, prefix_forum.course, count(*) as total FROM prefix_forum | |||
INNER JOIN prefix_course | |||
ON prefix_course.id = prefix_forum.course | |||
WHERE NOT(prefix_forum.type = 'news') | |||
GROUP BY prefix_forum.course | |||
ORDER BY total desc | |||
</code> | |||
===FORUM use Count per COURSE by type -- not including NEWS Forum!=== | |||
<code sql> | |||
SELECT prefix_course.fullname, prefix_forum.course, prefix_forum.type, count(*) as total FROM prefix_forum | |||
INNER JOIN prefix_course | |||
ON prefix_course.id = prefix_forum.course | |||
WHERE NOT(prefix_forum.type = 'news') | |||
GROUP BY prefix_forum.course,prefix_forum.type | |||
ORDER BY total desc | |||
</code> | </code> |
Revision as of 16:42, 13 July 2010
These are community contributed reports for the Custom_SQL_queries_report site-wide report plugin (module)
Everyone is welcome to add their own. until we find a better way to exchange reports between us.
Enjoy :-)
Detailed ACTIONs for each MODULE
SELECT module,action,count(id) as counter
FROM prefix_log
GROUP BY module,action
ORDER BY module,counter desc
Detailed ACTIONs for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)
SELECT r.name,l.action, count( l.userid ) as counter
FROM `prefix_log` as l
JOIN `prefix_role_assignments` AS ra on l.userid = ra.userid
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid IN (3,4,5)
GROUP BY roleid,l.action
ORDER BY counter desc
Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)
SELECT l.action, count( l.userid ) as counter , r.name
FROM `prefix_log` as l
JOIN `prefix_role_assignments` AS ra on l.userid = ra.userid
JOIN `prefix_role` AS r ON ra.roleid = r.id
WHERE (ra.roleid IN (3,4,5)) AND (l.action LIKE '%view%' )
GROUP BY roleid,l.action
order by r.name,counter desc
Most Active courses
SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
GROUP BY courseId
ORDER BY hits DESC
RESOURCE count for each COURSE
SELECT COUNT(l.id) count, l.course, c.fullname coursename
FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id
GROUP BY course
ORDER BY count DESC
Most popular ACTIVITY
SELECT COUNT(l.id) hits, module
FROM prefix_log l
WHERE module != 'login' AND module != 'course' AND module != 'role'
GROUP BY module
ORDER BY hits DESC
System wide use of ACTIVITIES and RESOURCES
SELECT count( cm.id ) AS counter, m.name
FROM `prefix_course_modules` AS cm
JOIN prefix_modules AS m ON cm.module = m.id
GROUP BY cm.module
ORDER BY counter DESC
LOG file ACTIONS per MODULE per COURSE (IDs)
select course,module,action,count(action) as summa from prefix_log
where action <> 'new'
group by course,action,module
order by course,module,action
"Compose Web Page" RESOURCE count
SELECT course,prefix_course.fullname, COUNT(*) AS Total
FROM `prefix_resource`
JOIN `prefix_course` ON prefix_course.id = prefix_resource.course
WHERE type='html'
GROUP BY course
Total Activity of Roles:"Teacher" and "None-Editing Teacher" by Dates and by Hours
The output columns of this report table can be used as base for a Pivot-Table which will show the amount of activity per hour per days in 3D graph view.
SELECT DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%Y/%m/%d' ) AS grptimed ,
DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%k' ) AS grptimeh , count( l.userid ) AS counter
FROM `prefix_log` AS l
JOIN prefix_user AS u ON u.id = l.userid
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_role AS r ON r.id = ra.roleid
WHERE ra.roleid IN (3,4)
GROUP BY grptimed,grptimeh
ORDER BY grptimed,grptimeh
FORUM use Count per COURSE -- not including NEWS Forum!
SELECT prefix_course.fullname, prefix_forum.course, count(*) as total FROM prefix_forum
INNER JOIN prefix_course
ON prefix_course.id = prefix_forum.course
WHERE NOT(prefix_forum.type = 'news')
GROUP BY prefix_forum.course
ORDER BY total desc
FORUM use Count per COURSE by type -- not including NEWS Forum!
SELECT prefix_course.fullname, prefix_forum.course, prefix_forum.type, count(*) as total FROM prefix_forum
INNER JOIN prefix_course
ON prefix_course.id = prefix_forum.course
WHERE NOT(prefix_forum.type = 'news')
GROUP BY prefix_forum.course,prefix_forum.type
ORDER BY total desc