selecting top 10 SUM()s

[Packetloss]

Hi,

I have a session tracking table where each entry keeps track of when a user logs in and logs out. When the user logs out, it stores the session length in seconds in the entry. Therefore, there would be many entries for one user who's logged in and out multiple times. How would I list the top 10 users with the longest accumlated session length? I guess it would have something to do with SUM()?

Many thanks,

Jared

[drzoid]

You are right, you need to use SUM() as well as GROUP BY. But why dont you simply add the duration of the session to a column of the actual user's table?

By the way, this should better have gone into http://www.sitepoint.com/forums/forumdisplay.php?f=88

[Packetloss]

Thanks drzoid,

I need to keep it in a seperate table because each entry tells what 'section' they were on during that session and I might need to do top 10 on a certain section as well. There are alot of sections. Sorry for not mentioning this in my last post.

If anyone can move my thread to the right forum, please do, thanks!

[drzoid]

I see. Well, if you could post the DB schema I might be able to come up with a query.

[Packetloss]

Here's my basic DB layout

user_info
-----------
user_id
username
password

user_sessions
-----------
session_id
user_id
section_id
session_time (time in seconds from start to end)
session_start (timestamp)
session_end (timestamp)

I just need to know the top 10 people with the largest SUM of session_time. Thanks again!

[drzoid]

I havent tried it, but something along these lines should do the trick

SELECT user_id, SUM(session_time) FROM user_sessions ORDER BY session_time DESC GROUP BY user_id

[r937]

i'm going to guess this is for mysql

select U.userid
     , U.username
     , sum(US.session_time) as total_time
  from user_info as U
inner
  join user_sessions as US
    on U.user_id
     = US.user_id
group
    by U.userid
     , U.username
order
    by total_time desc
limit 10