max count with joins
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 开发者_C百科 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.
Here you are:
SELECT u.login, t1.total_sess, t1.total_min, t2.mf, t2.sess_mf, t2.min_mf
FROM users u
LEFT JOIN (
SELECT userid, COUNT(minutes) AS total_sess, SUM(minutes) AS total_min
FROM sessions
GROUP BY userid
) AS t1 ON t1.userid = u.id
LEFT JOIN (
SELECT userid, name AS mf, COUNT(*) AS sess_mf, SUM(minutes) AS min_mf
FROM sessions s
JOIN computers c ON c.id = s.computerid
GROUP BY userid, computerid
HAVING COUNT(computerid) >= ALL(SELECT COUNT(*)
FROM sessions s2
WHERE s2.userid = s.userid
GROUP BY s2.computerid)
) AS t2 ON t2.userid = u.id
I'm using MySQL syntax, but it should be pretty portable.
If you need anything more, feel free to ask!
EDIT: I updated the query, the previous one was wrong :(
精彩评论