[SOLVED] MySQL Group by consecutive values and count

Issue

I have a table that looks like this

id
1
2
4
5
6
10
11

So a bunch of consecutive values, an unknown number of absent fields and then other consecutive values.
What I am trying to achieve is to get

id stint
1 0
2 0
4 1
5 1
6 1
10 2
11 2

By incrementing every time the number of the stint, which I can later use for summing over other columns.
Is it possible? Thanks

Solution

If your MySQL version support window function.

You can try to use LAG window function in subquery to get previous id column, then use SUM condition aggregate window function.

Query #1

SELECT Id,
       SUM(id - n_Id > 1) OVER(ORDER BY id) stint
FROM (
    SELECT *,LAG(id,1,id) OVER(ORDER BY id) n_Id 
    FROM T
) t1
Id stint
1 0
2 0
4 1
5 1
6 1
10 2
11 2

View on DB Fiddle

Answered By – D-Shih

Answer Checked By – Robin (BugsFixing Admin)

Leave a Reply

Your email address will not be published.