psrpsrpsr psrpsrpsr - 1 month ago 7
SQL Question

How can I count the number of groups/field values for an ID?

I have a table of detail for which I want to count user_ids that had 2 or more different values of action_taken.

Detail:

| user_id | action_taken | action_date |
|---------|-----------------------|-------------|
| 1234 | clicked on a link | 1/1/2017 |
| 1234 | went to the home page | 1/5/2017 |
| 1234 | clicked on a link | 1/7/2017 |
| 1234 | clicked on a link | 1/9/2017 |
| 1234 | changed password | 1/11/2017 |
| 1234 | clicked on a link | 1/13/2017 |
| 9876 | went to the home page | 2/1/2017 |
| 9876 | went to the home page | 2/5/2017 |
| 9876 | went to the home page | 2/7/2017 |
| 9876 | went to the home page | 2/9/2017 |
| 5566 | clicked on a link | 1/1/2017 |
| 5566 | clicked on a link | 1/5/2017 |
| 5566 | changed password | 1/7/2017 |
| 5566 | clicked on a link | 1/9/2017 |
| 4433 | went to the home page | 1/5/2017 |


Desired output:

Output 1: Return the number of different values of actions_taken.

| user_id | number_dift_action_taken_values |
|---------|---------------------------------|
| 1234 | 3 |
| 4433 | 1 |
| 5566 | 2 |
| 9876 | 1 |


Output 2: Return only the user_ids that had >=2 different values of action_taken.

| user_id |
|---------|
| 1234 |
| 5566 |


Here's what I have so far that isn't working: http://rextester.com/TUL87833. The HAVING BY clause is counting number of rows in the detail that belong to each group, not
the number of number of groups specified by the GROUP BY user_id, action_taken clause...

select
user_id
,action_taken
,count(*)
from
tbl
group by
user_id
,action_taken
having count(*) >=2;

| user_id | action_taken | count |
|---------|-----------------------|-------|
| 1234 | clicked on a link | 4 |
| 5566 | clicked on a link | 3 |
| 9876 | went to the home page | 4 |

Answer Source

Output 1

SELECT user_id,
       COUNT(DISTINCT action_taken) number_dift_action_taken_values
  FROM t_tab
 GROUP BY user_id

Result

user_id number_dift_action_taken_values
1234    3
4433    1
5566    2
9876    1

Output 2

SELECT user_id
  FROM t_tab
 GROUP BY user_id
HAVING COUNT(DISTINCT action_taken) >= 2

Result

user_id
1234
5566