Mon Tue Wed Thu Fri Sat Sun Total Avg
User 1 - - - - - - - - -
User 2 - - - - - - - - -
User 3 - - - - - - - - -
User 4 - - - - - - - - -
User 5 - - - - - - - - -
Each cell will contain a number of logins of a certain user (for each week day, total and average). The login table that I need to generate this report from looks like this (LoginDate is an int, generated by PHP's time()):
So basically each record in the table is a login of a certain user for a certain date. Now I was wondering what would be an efficient query to generate the report?
an ORDER BY on User ID and the day of week (extracted using DATE_FORMAT() and FROM_UNIXTIME()) .. a COUNT() might be of use too, or a DISTINCT, depending on how you want information to be aggregated.
GROUP BY is easy of course, but COUNT(ID) + GROUP BY would only return the total count of logins for each learner. But how do I 'explode' the total login count to weekdays?