How to query Data Group by with Order by in Oracle

Question

I have sample data like this

CREATE TABLE table_name (aktif, "START", "END", NO_BOX, QTY) AS
SELECT 1, 'A0001', 'A0020', 2016, 100 FROM DUAL UNION ALL
SELECT 1, 'A0021', 'A0040', 2016, 100 FROM DUAL UNION ALL
SELECT 1, 'A0041', 'A0060', 2016, 100 FROM DUAL UNION ALL
SELECT 0, 'A0061', 'A0080', NULL, 100 FROM DUAL UNION ALL
SELECT 0, 'A0081', 'A0100', NULL, 100 FROM DUAL UNION ALL
SELECT 1, 'A0101', 'A0120', 2016, 100 FROM DUAL UNION ALL
SELECT 1, 'A0121', 'A0140', 2016, 100 FROM DUAL UNION ALL
SELECT 1, 'A0141', 'A0160', 2016, 100 FROM DUAL UNION ALL
SELECT 0, 'A0161', 'A0180', NULL, 100 FROM DUAL UNION ALL
SELECT 0, 'A0181', 'A0200', NULL, 100 FROM DUAL;

I want to group by the columns where AKTIF and NO_BOX remain the same based on the order of the rows and then select SUM(QTY), MIN(START), MAX(END).

The output should be:

AKTIF	START	END	NO_BOX	QTY
1	A0001	A0060	2016	300
0	A0061	A0100	NULL	200
1	A0101	A0160	2016	300
0	A0161	A0200	NULL	200

Answer 1

At the end, that's gaps and islands problem.

Sample data:

SQL> with test (aktif, cstart, end, no_box, qty) as
  2    (select 1, 'A0001', 'A0020', 2016, 100 from dual union all
  3     select 1, 'A0021', 'A0040', 2016, 100 from dual union all
  4     select 1, 'A0041', 'A0060', 2016, 100 from dual union all
  5     --
  6     select 0, 'A0061', 'A0080', null, 100 from dual union all
  7     select 0, 'A0081', 'A0100', null, 100 from dual union all
  8     --
  9     select 1, 'A0101', 'A0120', 2016, 100 from dual union all
 10     select 1, 'A0121', 'A0140', 2016, 100 from dual union all
 11     select 1, 'A0141', 'A0160', 2016, 100 from dual union all
 12     --
 13     select 0, 'A0161', 'A0180', null, 100 from dual union all
 14     select 0, 'A0181', 'A0200', null, 100 from dual
 15    ),

Query begins here:

 16  temp as
 17    (select t.*,
 18       row_number() over (order by cstart) -
 19       row_Number() over (partition by aktif order by cstart) grp
 20     from test t
 21    )
 22  select aktif,
 23    min(cstart) cstart,
 24    max(end) end,
 25    no_box,
 26    sum(qty) qty
 27  from temp
 28  group by aktif, no_box, grp
 29  order by cstart;

     AKTIF CSTAR END       NO_BOX        QTY
---------- ----- ----- ---------- ----------
         1 A0001 A0060       2016        300
         0 A0061 A0100                   200
         1 A0101 A0160       2016        300
         0 A0161 A0200                   200

SQL>