CODEWITHSUNDEEP
sqldatabasesqlite
Hassan Hayat
Hassan Hayat

Reputation: 1055

Compute a running count in sqlite

I would like to know how to compute a running count in SQLite as follows

Given the following table:

id | value
-- | -----
 A |    10
 A |    20
 B |    40
 A |    15
 B |    20
 C |    30

I would like to get the following table:

id | value | running_count
-- | ----- | -------------
 A |    10 |             1
 A |    20 |             2
 B |    40 |             1
 A |    15 |             3
 B |    20 |             2
 C |    30 |             1

Where, for example, the row with id = A and value = 15 has a running_count = 3 because this is the 3rd row with an ID of A.

Note: this solution should work for arbitrarily large tables. Thanks.

Upvotes: 0

Views: 530

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Alas, SQLite doesn't support window functions. You can do this with a correlated subquery. However, you need a proper ordering column. In SQLite, I think you can use rowid:

select t.*,
       (select count(*)
        from t t2
        where t2.id = t.id and t2.rowid <= t.rowid
       ) as running_count
from t;

Upvotes: 1

Related Questions