How to group by continuous rows in sqlite?

Question

I have a table like:

id version count
1  0       3
2  0       4
3  0       3
4  1       3
5  1       2
6  1       1
7  0       3
8  0       5

I want to get a result like:

min_id version sum
1      0       10
4      1       6
7      0       8

If I use SELECT MIN(id), version, sum(count) group by version I get this:

min_id version sum
1      0       18
4      1       6

Because GROUP BY combines everything in the same version. I want to combine only those versions which are continuous, based on id.

Answer 1

This is hard to do in SQLite, but possible. Now, the performance is awful, but the idea is that you count the number of rows before any given row with a different id. This identifies each group! Voila!

select version, min(id), max(id), sum(count)
from (select t.*,
             (select count(*) from t t2 where t2.version <> t.version and t2.id < t.id) as grp
      from t
     ) t
group by version, grp;