0

I have a table similar to:

user_id     |   action

 1                a1                  
 2                a1
 1                a1
 3                a1
 2                a1
 1                a2
 2                a2

I have to find out the number of users who have done action a1, group by number of actions and number of users for a1.

eg in the above table, the output should be:

num_users          num_action_a1
1 (user: 3)             1
2  (user: 1,2)          2

I tried following query

select user_id, count(user_id) from table where action = 'a1' group by user_id

Do I have to use nested group by ?

Saurabh Verma
  • 6,328
  • 12
  • 52
  • 84
  • 2
    What query did you try? – Gunaseelan Jul 08 '14 at 05:56
  • select user_id, count(user_id) from table where action = 'a1' group by user_id - I'm confused about using 2 group by – Saurabh Verma Jul 08 '14 at 06:00
  • Check this http://stackoverflow.com/questions/1841426/is-it-possible-to-group-by-multiple-columns-using-mysql – Gunaseelan Jul 08 '14 at 06:02
  • Again tried: select user_id, count(user_id) from table where query_type = 'a1' group by user_id, count(user_id) - Didn't work :( – Saurabh Verma Jul 08 '14 at 06:08
  • Try this SELECT count(user_id) num_users FROM table WHERE action=a1 GROUP BY (SELECT count(user_id) no_users FROM table ASC),(SELECT count(user_id) FROM table WHERE action=a1) – Gunaseelan Jul 08 '14 at 06:16
  • I'm getting this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC),(SELECT count(user_id) FROM action_table WHERE query_type = 'a1')' at line 1 – Saurabh Verma Jul 08 '14 at 06:20
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/56923/discussion-between-guna-seelan-and-saurabh-verma). – Gunaseelan Jul 08 '14 at 06:28

1 Answers1

1

Using a sub query to get the counts per user id, then selecting from that the users and counts:-

SELECT num_users, GROUP_CONCAT(user_id) AS actual_users , COUNT(*) AS num_action_a1
FROM
(
    SELECT user_id, COUNT(*) AS num_users
    FROM some_table
    WHERE action = 'a1'
    GROUP BY user_id
) sub0
GROUP BY num_users
Kickstart
  • 21,403
  • 2
  • 21
  • 33