1

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.

0

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
| improve this answer | |
  • 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 '16 at 10:24
  • Currently, it's taking 92-130 seconds to run the query. – Mansoor Apr 23 '16 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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