max count with joins

Posted by trixet on Stack Overflow See other posts from Stack Overflow or by trixet
Published on 2010-04-08T17:08:06Z Indexed on 2010/04/08 17:13 UTC
Read the original article Hit count: 281

Filed under:
|
|
|

I have 3 tables:

users:

Id   Login
1    John
2    Bill
3    Jim

computers:

Id   Name
1    Computer1
2    Computer2
3    Computer3
4    Computer4
5    Computer5

sessions:

UserId   ComputerId   Minutes
1        2            47
2        1            32
1        4            15
2        5            5
1        2            7
1        1            40
2        5            31

I would like to display this resulting table:

Login   Total_sess   Total_min   Most_freq_computer   Sess_on_most_freq   Min_on_most_freq
John    4            109         Computer2            2                   54
Bill    3            68          Computer5            2                   36
Jim     -            -           -                    -                   -

Myself I can only cover first 3 columns with:

SELECT Login, COUNT(sessions.UserId), SUM(Minutes) FROM users
LEFT JOIN sessions
ON users.Id = sessions.UserId GROUP BY users.Id

And some kind of other columns with:

SELECT main.*
FROM (SELECT UserId, ComputerId, COUNT(*) AS cnt ,SUM(Minutes)
FROM sessions
GROUP BY UserId, ComputerId) AS main
INNER JOIN (
SELECT ComputerId, MAX(cnt) AS maxCnt FROM (
SELECT ComputerId, UserId, COUNT(*) AS cnt FROM sessions GROUP BY ComputerId, UserId
)
AS Counts GROUP BY ComputerId) 
                    AS maxes
ON main.ComputerId = maxes.ComputerId
AND main.cnt = maxes.maxCnt

But I need to get whole resulting table in one query. I feel I'm doing something completely wrong. Need help.

© Stack Overflow or respective owner

Related posts about sql

Related posts about select