max count with joins
- by trixet
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.
