SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    selecting top 10 SUM()s

    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
    My links: [ Blog ] - [ deviantArt ]

  2. #2

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    My links: [ Blog ] - [ deviantArt ]

  4. #4

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see. Well, if you could post the DB schema I might be able to come up with a query.

  5. #5
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    My links: [ Blog ] - [ deviantArt ]

  6. #6

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I havent tried it, but something along these lines should do the trick
    Code:
    SELECT user_id, SUM(session_time) FROM user_sessions ORDER BY session_time DESC GROUP BY user_id

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm going to guess this is for mysql
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •