I want to group table entries for each userId only if they are consecutive entries in the table. I have a table like this.

id userId      transactionTime

1  1           2016-04-13 22:15:44
2  1           2016-04-13 22:40:22
3  1           2016-04-13 22:41:34
4  2           2016-04-13 22:41:34
5  2           2016-04-13 22:42:25
6  1           2016-04-13 23:43:55   

Expected Result:

userId    count
1         3
2         2
1         1    

Thanks :)

Update: The transactionTime is ordered in asc. Considering this, we can check the consecutive occurence of same userId and make it a single entry with number of counts.

Consider this, userId=1 occurs three consecutive times from id=1,2,3 this will be first result with userId=1 and count=3. Similarly at id=4 and 5 userId is 2 which will be second row in the result with userId=1 and count=2.

1 Answer 1


You can use variables for this kind of thing:

select userId, count(*)
from (
    select @g:=case when userId=@lastuserId then @g else @g+1 end AS grp,
           @lastuserId:=userId AS userId
    from test1, (select@lastuserId:=0, @g:=0) vars
    order by transactionTime
) groups
group by grp, userId
order by grp
  • Thanks... Which is a better option? Whether to manipulate data in java after fetching initial table or just run the complex query you've provided.?
    – Mansoor
    Apr 23, 2016 at 10:24
  • Currently, it's taking 92-130 seconds to run the query.
    – Mansoor
    Apr 23, 2016 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.