MySQL group by number series

Question

I'm just starting to learn MySQL and I have encountered this problem which I badly needed the solution or just the logic.

For example I have this table:

id         a 
--        --
 1         1
 2         2
 3         3
 4         5
 5         6
 6         7
 7         9
 8        10
 9        11
10        12

Now, what I want is to display all the data in column a which should be grouped by series. In this case the result should be:

series_start|series_end|count
------------+----------+-----
           1          3     3
           5          7     3
           9         12     4

This needs a lot of subqueries and joins. I just can't figure it out by now.

Answer 1

This is a gaps-and-islands problem, and here's another method of solving it, which also uses variables:

SELECT
  MIN(a) AS series_start,
  MAX(a) AS series_end,
  MAX(a) - MIN(a) + 1 AS series_count
FROM (
  SELECT
    a,
    @r := @r + 1 AS r
  FROM
    yourtable,
    (SELECT @r := 0) AS x
  ORDER BY
    a
) s
GROUP BY
  a - r
ORDER BY
  a - r
;

This is how it works.

The subquery assigns row numbers to the table rows and returns this row set:

 a   r
--  --
 1   1
 2   2
 3   3
 5   4
 6   5
 7   6
 9   7
10   8
11   9
12  10

In this case the r column, which stores the row numbers, happens to match the id column in your data sample, but I'm assuming that in general the id column may have gaps, and for that reason it cannot be used here.

The main query groups the results by the difference between r and a: for sequential values, it will always be the same:

 a   r  a - r
--  --  -----
 1   1      0
 2   2      0
 3   3      0
 5   4      1
 6   5      1
 7   6      1
 9   7      2
10   8      2
11   9      2
12  10      2

and that allows us to group such rows together. All that remains at this point is to get the minimim, maximum and count, which gives you this output:

series_start  series_end  series_count
------------  ----------  ------------
           1           3             3
           5           7             3
           9          12             4

A SQL Fiddle demonstration of this query, for which I've borrowed @sgeddes's schema, can be found here.

---

UPDATE

As numeric variables cannot be used (according to comments), you could assign row numbers using a triangular self-join, but it will be much less efficient than using a variable. Anyway, here's the modified version, changes to the previous query being highlighted in bold:

SELECT
  MIN(a) AS series_start,
  MAX(a) AS series_end,
  MAX(a) - MIN(a) + 1 AS series_count
FROM (
  SELECT
    data.a,
    COUNT(*) AS r
  FROM
    yourtable AS data
  INNER JOIN
    yourtable AS tally
  ON
    data.id >= tally.id
  GROUP BY
    data.a
) s
GROUP BY
  a - r
ORDER BY
  a - r
;

The approach itself remains unchanged: the subquery returns a ranked row set, which is then processed same as previously.

A SQL Fiddle demo for the modified query is available here.

Answer 2

Here's one solution using user defined variables:

select min(series_start) series_start, 
  max(series_end) series_end,
  1 + max(series_end) - min(series_start) count
from (
  select t1.a series_start, 
    t2.a series_end,
    @val:=IF(@prev=t2.a-1,@val,@val+1) val,
    @prev:=t2.a
  from yourtable t1
    join yourtable t2 on t1.a = t2.a-1
    join (select @val:= 0, @prev:= 0) t
  order by t2.a
  ) t
group by val

• SQL Fiddle Demo