I have the following table structure:
| id | user_id | action_type | action_start_time | action_end_time |
|---|---|---|---|---|
| 1 | 5 | 4 | 2023-06-21 16:52:49.111 | 2023-06-21 16:55:49.022 |
| 2 | 3 | 6 | 2023-06-21 16:55:51.052 | 2023-06-21 17:10:19.100 |
| 3 | 5 | 4 | 2023-06-21 18:38:44.062 | 2023-06-21 18:59:51.025 |
| 4 | 16 | 1 | 2023-06-21 19:52:39.426 | 2023-06-21 20:01:49.127 |
| 5 | 5 | 1 | 2023-06-21 20:11:19.321 | 2023-06-21 21:55:49.132 |
And I need to calculate the total time each user spent per action_type.
I only managed to work with DATEDIFF to sum the number of seconds per row:
SELECT DATEDIFF(SECOND, action_start_time, action_end_time) AS TotalSeconds from my_table;
But I was not sure about how to group by each user id and action type.
>Solution :
I was not sure about how to group by each user id and action type
You simply do almost exactly what you say and group by those columns:
select user_id, action_type,
sum(DateDiff(second, action_start_time, action_end_time)) as TotalSeconds
from my_table
group by user_id, action_type;