# Thread: Group by two columns?

1. ## 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.

2. 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. 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?

4. 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. Thanks alot

6. 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?

7. Hi. anyone?

#### Posting Permissions

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