Leandro Mauro Leandro Mauro -4 years ago 63
SQL Question

how to query min and max values from table and show it like ranges/sequences?

I have a table like this one (table):

id | from | to | count
1 | 1 | 25 | 25
1 | 26 | 50 | 25
1 | 76 | 100 | 25
1 | 1501 | 1525 | 25


and what I want is group in "full" ranges like this one:

id | from | to | count
1 | 1 | 50 | 50
1 | 76 | 100 | 25
1 | 1501 | 1525 | 25


I'm using MySQL and my (horrible) query doesn't work properly because I don't return what I want.

SELECT id, MIN(from), MAX(to), SUM(count)
FROM table
GROUP BY id
HAVING (MAX(to)-MIN(from)+1) = SUM(count)

Answer Source

Note the following data set is a little more complicated than that provided in the question...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,val_from  INT NOT NULL
,val_to INT NOT NULL
,count INT NOT NULL
);

INSERT INTO my_table VALUES
(1,1,25,25),
(2,26,35,12),
(3,36,50,13),
(4,76,100,25),
(5,1501,1525,25);


SELECT MIN(val_from) start
     , MAX(val_to) finish
     , SUM(count) total 
  FROM 
     (
       SELECT *
            , CASE WHEN val_from <= @prev_val_to + 1 THEN @i := @i ELSE @i:=@i+1 END i
            , @prev_val_to := val_to prev_val_to 
         FROM my_table
            , (SELECT @prev_val_to := null, @i:=1) vars 
        ORDER 
           BY id
     ) x
GROUP BY i;
+-------+--------+-------+
| start | finish | total |
+-------+--------+-------+
|     1 |     50 |    50 |
|    76 |    100 |    25 |
|  1501 |   1525 |    25 |
+-------+--------+-------+
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download