MySQL Question
Please, I need your help guys to solve this
I am new in sql and I need some help. I have a table with 4 columns (user_id; type: string, action; type string, time; type: timestamp, session_id: string)
| id | action | time | session_id |
+------+--------+---------------------+------------+
| 1 | action_1 | 2016-10-01 09:00:00 | 1111 |
| 1 | action_2 | 2016-10-01 15:00:00 | 1111 |
| 1 | xxxx | 2016-10-01 19:10:00 | 1111 |
| 1 | action_4 | 2016-10-01 20:00:00 | 1111 |
| 1 | action_1 | 2016-11-02 13:00:00 | 1122 |
| 1 | yyyy | 2016-11-02 16:00:00 | 1122 |
| 1 | action_3 | 2016-11-02 23:00:00 | 1122 |
| 2 | action_1 | 2016-11-01 20:30:00 | 2222 |
| 2 | action_2 | 2016-11-01 20:50:41 | 2222 |
| 2 | yyyy | 2016-11-01 21:00:00 | 2222 |
| 2 | action_4 | 2016-11-01 21:30:00 | 2222 |
| 3 | action_1 | 2016-11-04 09:50:00 | 3333 |
| 3 | action_2 | 2016-11-04 10:00:00 | 3333 |
| 3 | action_3 | 2016-11-04 10:10:00 | 3333 |
| 3 | action_4 | 2016-11-04 10:25:00 | 3333 |
| 3 | action_5 | 2016-11-04 10:40:00 | 3333 |
| 3 | xxxx | 2016-11-04 11:00:00 | 3333 |
| 3 | action_7 | 2016-11-04 15:00:00 | 3333 |
("xxxx" and "yyyy" are any actions which should be found).
The question is: number of cases when a specific action is the penultimate action in a session (the final result of the query should be per action and should look like:
action | count(action)
-----------------------
Action | Number
......
Thanks in advance.
Answer
Assuming that you want count the occurrence for xxxx and yyyy You should use in a group by
select action, count(*)
from my_table
where action in ('xxxx', 'yyyy')
group by action
Source (Stackoverflow)