SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict ewiz's Avatar
    Join Date
    Dec 2003
    Location
    Egypt
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group by two columns?

    Hi.. I have a table with userid, actionid, actiontimes, and actiondate. It stores this: when does user take one of some actions and how many times at that date.

    I need this:
    Over a month, what actions did every user take and for how many times?

    Which means that I need to group by userid and also actionid. Is there a way for this?

    Thanks.
    add your hosting company and plans for free
    Ahsan Host
    my business site
    Internet Plus

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your data looks like this:
    Code:
    UserID      ActionID    ActionTimes ActionDate         
    ----------- ----------- ----------- -------------------
    1           100         7           2005-03-23 21:43:00
    1           101         5           2005-03-23 21:43:00
    1           102         4           2005-03-23 21:43:00
    2           100         3           2005-03-23 21:43:00
    2           104         9           2005-03-23 21:44:00
    3           103         3           2005-03-23 21:44:00
    1           100         8           2005-03-23 21:46:00
    2           100         4           2005-03-23 21:46:00
    1           100         11          2005-02-23 21:50:00
    2           100         12          2005-02-23 21:51:00
    3           100         13          2005-02-23 21:51:00
    Then this:
    Code:
    SELECT UserID, ActionID, Sum(ActionTimes) as [Total Action Times]
    FROM TABLE1
    GROUP BY UserID, ActionID
    ORDER BY UserID, ActionID
    Returns:
    Code:
    UserID      ActionID    Total Action Times 
    ----------- ----------- ------------------ 
    1           100         26
    1           101         5
    1           102         4
    2           100         19
    2           104         9
    3           100         13
    3           103         3

    Incorporating the month factor..

    This query:
    Code:
    SELECT UserID, ActionID, Month(ActionDate) as [Month], Sum(ActionTimes) as [Total Action Times]
    FROM TABLE1
    GROUP BY UserID, ActionID, Month(ActionDate)
    ORDER BY Month(ActionDate), UserID, ActionID
    Returns:
    Code:
    UserID      ActionID    Month       Total Action Times 
    ----------- ----------- ----------- ------------------ 
    1           100         2           11
    2           100         2           12
    3           100         2           13
    1           100         3           15
    1           101         3           5
    1           102         3           4
    2           100         3           7
    2           104         3           9
    3           103         3           3
    How's that?

    Dan

  3. #3
    SitePoint Addict ewiz's Avatar
    Join Date
    Dec 2003
    Location
    Egypt
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How is that? That is GREAT Thanks alot. Worked like charm..
    I am excited now so I can't analyse that. Can you explain? I mean when grouping by more than one column how things should be written?
    add your hosting company and plans for free
    Ahsan Host
    my business site
    Internet Plus

  4. #4
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not easy to explain in just a few sentences, but if you experiment a little, you'll see that it's basically just a matter of adding the 2nd col to the SELECT list and then again to the GROUP BY clause.

    Another way to think of it is that the GROUP BY clause collapses your data and the aggregate function (Sum(), Count(), etc. -not in the GROUP BY clause) measures the collapse in a certain way. The number of fields in the GROUP BY clause determine the granularity of the collapse. Frequently, the fields in the SELECT list match the fields in the GROUP BY clause. Again, with the exception of the aggregating function.

    There's a universe of dimensions in GROUP BY. Practice helps build your understanding.

    Dan

  5. #5
    SitePoint Addict ewiz's Avatar
    Join Date
    Dec 2003
    Location
    Egypt
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks alot
    add your hosting company and plans for free
    Ahsan Host
    my business site
    Internet Plus

  6. #6
    SitePoint Addict ewiz's Avatar
    Join Date
    Dec 2003
    Location
    Egypt
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.. I am back

    What if I want to limit the returned values like this:
    Over a month, what are the top 10 most frequent - actions did every user take and for how many times?
    add your hosting company and plans for free
    Ahsan Host
    my business site
    Internet Plus

  7. #7
    SitePoint Addict ewiz's Avatar
    Join Date
    Dec 2003
    Location
    Egypt
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi. anyone?
    add your hosting company and plans for free
    Ahsan Host
    my business site
    Internet Plus


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
  •