CODEWITHSUNDEEP
mysqlsql
user2693017
user2693017

Reputation: 1870

MySQL get results for different timespans and group

I have a big table with user "session" data (login/logout time and the use_time). Now I would like to create a top list, with:

The table with the data looks like this:

id | user_id | login_time (datetime) | logout_time (datetime) | online_time (int in seconds)

Unfortunately I haven't found a way to get this done with one query.

SELECT sum(s.online_time) as ontime, u.name as name FROM session_user s INNER JOIN users u ON u.id=s.user_id WHERE login_time > NOW() - INTERVAL 1 DAY GROUP BY u.id

This query does give me the daily time of each active user, but I also want the other 3 (total time, week time, month time).

Is it possible to get that done with one query and if possible within a reasonable time?

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use conditional aggregation to get this information:

SELECT u.name, sum(s.online_time) as oneime,
       sum(case when login_time > NOW() - INTERVAL 1 DAY then s.online_time end) as ontime_1day,
       sum(case when login_time > NOW() - INTERVAL 7 DAY then s.online_time end) as ontime_1week,
       sum(case when login_time > NOW() - INTERVAL 1 MONTH then s.online_time end) as ontime_1month
FROM session_user s INNER JOIN
     users u
     ON u.id=s.user_id
GROUP BY u.id;

Upvotes: 2

Related Questions