How to get consecutive numbers using mysql?

Question

I want to retrieve the consecutive numbers and its counts, for example in my db i have inputs like

1,2,3,4,5,9,10,12,14,15,16,51,81

I expected output

Numbers         counts
  1-5              5
  9-10             2
 12-16             4
   51              1
   81              1

Please share the solution if anyone knows....

[Show your table structure](https://stackoverflow.com/questions/1498777/how-do-i-show-the-schema-of-a-table-in-a-mysql-database) — Martin, May 24 '17 at 07:04
I have one column like studentId in my table, In that column the values are not continuous fully some break points are there(break point means some value are missing for example 1,2,3,4,5,8,9,10,15,23 like that). so i want to get the count how many continuous are in that table and that no starting point and ending point — Vinothkumar Manogar, May 24 '17 at 07:09
FWIW, this strikes me as something that is challenging to do in a SQL query, but would be quite easy to do in backend code with, say, PHP. — Cato Minor, May 24 '17 at 07:14
@CatoMinor Funny, that I think, it's the other way round...easy to do in sql, tedious to do in PHP... — fancyPants, May 24 '17 at 07:20
@fancyPants sounds like you're writing us a solution, even as I type? `:-D` — Martin, May 24 '17 at 07:21
Hm, maybe my opinion comes from me being a database admin and having no clue, how to approach this in PHP :-D And now I noticed there are more implications than I first expected, but it's still doable. — fancyPants, May 24 '17 at 07:24
This is not feasible in SQL. Use your backend code like PHP or C#. — Niels Keurentjes, May 24 '17 at 07:24
If any one known please share the PHP code for that solution — Vinothkumar Manogar, May 24 '17 at 07:33
@NielsKeurentjes It is feasible ;-) see my answer, if you like. — fancyPants, May 24 '17 at 07:34
I know I should've used "undesirable" instead. I usually prefer to tell people something's not doable instead because otherwise they might try, and this code will end up running some mission-critical production application somewhere where I have to clean it up in 5 years. — Niels Keurentjes, May 24 '17 at 07:39
@NielsKeurentjes Hm, good point. Haven't thought about that :-) — fancyPants, May 24 '17 at 07:40

score 3 · Answer 1 · answered May 24 '17 at 07:33

DISCLAIMER: This is for the fun of it. It may be easier to use an actual programming language of your choice.

This is not as easy as it first looks, you have to use user-defined variables.

Sample data:

CREATE TABLE t
    (`id` int)
;

INSERT INTO t
    (`id`)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (9),
    (10),
    (12),
    (14),
    (15),
    (16),
    (51),
    (81)
;

Query:

SELECT CONCAT_WS('-', MIN(id), IF(MAX(id) = MIN(ID), NULL, MAX(id))), COUNT(*)
FROM (
SELECT 
id
, @group_number := IF(@prev != id - 1, @group_number + 1, @group_number) AS gn
, @prev := id
FROM 
t,
(SELECT @group_number := 0, @prev := NULL) var_init_subquery
ORDER BY id
) sq
GROUP BY gn

Result:

| CONCAT_WS('-', MIN(id), IF(MAX(id) = MIN(ID), NULL, MAX(id))) | COUNT(*) |
|---------------------------------------------------------------|----------|
|                                                           1-5 |        5 |
|                                                          9-10 |        2 |
|                                                            12 |        1 |
|                                                         14-16 |        3 |
|                                                            51 |        1 |
|                                                            81 |        1 |

see it working live in an sqlfiddle

Hi, this is great, but is there a way to make it distinct? – Patrick May 12 '20 at 06:45 — Patrick, May 12 '20 at 06:45

score 0 · Answer 2 · answered May 12 '20 at 06:46

+-----------------------+----------+
| cheque_no Ascending 1 | COUNT(*) |
+-----------------------+----------+
| 0                     |        1 |
| 69843-69867           |       25 |
| 69867-69919           |       53 |
| 69919-69922           |        4 |
| 69922-69923           |        2 |
| 69923-69924           |        2 |
| 69924-69925           |        2 |
| 69925-69926           |        2 |
| 69926-69929           |        4 |
| 69929-69930           |        2 |
| 69930-69931           |        2 |
| 69931-69932           |        2 |
| 69932-69936           |        5 |
| 69936-69937           |        2 |
| 69937-70000           |       64 |
| 74101-74106           |        6 |
| 74108-74148           |       41 |
+-----------------------+----------+

Although after reading the disclaimer, i might just do that. Just need a rush option for now

How to get consecutive numbers using mysql?

2 Answers2

Linked