General developer forum

Obtaining Unique total users per month using SQL query

 
Picture of JC Dodo
Obtaining Unique total users per month using SQL query
 

Hello everyone!

I have Moodle 2.5 and 2.6 versions. 

I am just wondering if the below SQL query is a correct approach to obtain unique total users every month?

SELECT id,username,from_unixtime(`lastlogin`) AS `days` from <database name>.<prefix>_user where date(from_unixtime(`lastlogin`)) between date ('2017-12-01 00:00:00') and date('2017-12-31 23:59:59');


I know that this can be done using this ad-hoc contributed report - Distinct user logins per month but this is only effective above version 2.7 of Moodle.


Any advise is deeply appreciated.

Thanks!

 
Average of ratings: -
Picture of Darko Miletić
Re: Obtaining Unique total users per month using SQL query
Core developersParticularly helpful Moodlers

I think this one would be more appropriate:

SELECT COUNT(p.userid) nrusers FROM (
SELECT l.userid, MONTH(FROM_UNIXTIME(l.time))
FROM mdl_log l
WHERE l.action = 'login' AND l.module = 'user' AND YEAR(FROM_UNIXTIME(l.time)) = 2018
GROUP BY l.userid, MONTH(FROM_UNIXTIME(l.time))
) p

The reason we do not do count in first query is because in recent iterations MySQL insists that all fields in SELECT clause must be present in the GROUP BY clause as well. You can read more about that here:

https://mysqlserverteam.com/mysql-5-7-only_full_group_by-improved-recognizing-functional-dependencies-enabled-by-default/


 
Average of ratings: Useful (2)
Picture of JC Dodo
Re: Obtaining Unique total users per month using SQL query
 

Thanks Darko!

As I am new in MySQL, with that SQL query how would you specify to output the month and year i.e. december 2017 ?


Another question is would that count a for unique users?


 
Average of ratings: -