Possible to group by counts?

Question

I am trying to change something like this:

Index   Record   Time
1        10      100
1        10      200
1        10      300
1        10      400
1        3       500
1        10      600
1        10      700
2        10      800
2        10      900
2        10      1000
3        5       1100
3        5       1200
3        5       1300

into this:

Index  CountSeq     Record   LastTime
1       4            10      400
1       1            3       500
1       2            10      700
2       3            10      1000
3       3            5       1300

I am trying to apply this logic per unique index -- I just included three indexes to show the outcome.

So for a given index I want to combine them by streaks of the same Record. So notice that the first four entries for Index 1 have Records 10, but it is more succinct to say that there were 4 entries with record 10, ending at time 400. Then I repeat the process going forward, in sequence.

In short I am trying to perform a count-grouping over sequential chunks of the same Record, within each index. In other words I am NOT looking for this:

select index, count(*) as countseq, record, max(time) as lasttime
from Table1
group by index,record

Which combines everything by the same record whereas I want them to be separated by sequence breaks.

Is there a way to do this in SQL?

score 0 · Answer 1 · edited May 23 '17 at 12:01

It's hard to solve your problem without having a single primary key, so I'll assume you have a primary key column that increases each row (primkey). This request would return the same table with a 'diff' column that has value 1 if the previous primkey row has the same index and record as the current one, 0 otherwise :

SELECT *,
IF((SELECT index, record FROM yourTable p2 WHERE p1.primkey = p2.primkey)
= (SELECT index, record FROM yourTable p2 WHERE p1.primkey-1 = p2.primkey), 1, 0) as diff
FROM yourTable p1

If you use a temporary variable that increases each time the IF expression is false, you would get a result like this :

primkey  Index   Record   Time    diff
1        1        10      100     1
2        1        10      200     1
3        1        10      300     1
4        1        10      400     1
5        1        3       500     2
6        1        10      600     3
7        1        10      700     3
8        2        10      800     4
9        2        10      900     4
10       2        10      1000    4
11       3        5       1100    5
12       3        5       1200    5
13       3        5       1300    5

Which would solve your problem, you would just add 'diff' to the group by clause. Unfortunately I can't test it on sqlite, but you should be able to use variables like this. It's probably a dirty workaround but I couldn't find any better way, hope it helps.

That will group _all_ records, not just _sequential_ records. That requirement is not stated explicitly but can be inferred from the desired results. — D Stanley, Nov 07 '16 at 17:29

Possible to group by counts?

1 Answers1