Mysql – How to group records in thesql query

MySQL

I have a table (user_access) with the following columns in mysql database

id      int
user    int
access_time    datetime

I am trying to run a query that gives me the number of times users accesses the system (access_time) every 15 minutes between two specified timestamps. I am still developing the query, this is what I have now. Any suggestion will be highly welcomed.

select user, count(user) as users from user_access
where (access_time between '2013-05-28 02:00:00' and '2013-05-28 10:00:00')
group by user

Sample Result will be something like:

Time                User       No of Times
--------------------------------------------------
8:00am - 8:15am     user1          20
8:00am - 8:15am     user2          5
8:15am - 8:30am     user1          15
8:15am - 8:30am     user2          23

Best Answer

This query should do it for you

select 
    tm,(tm + interval 15 minute) tm2,user,users
from 
(
    select tm,user,COUNT(1) users from
    (
        select user,(access_time - interval
        MOD(unix_timestamp(access_time),900) second) tm
        from user_access where access_time between
        '2013-05-28 02:00:00' and '2013-05-28 10:00:00'
    ) AA
    group by tm,user
) A;

I'll leave the formatting to you

Give it a Try !!!

Related Question