Re: Comparing Rows

Ok, sorry about that. Below is the query to create the table and data. I have to count the number of iterations of a product per category (above and below layer 8).

CREATE TABLE t1 (prod_num NUMBER, device_num NUMBER, layer_name VARCHAR2(5), rev VARCHAR2(1));

INSERT INTO t1 VALUES(7239,    9032,    '1' ,      'A');
INSERT INTO t1 VALUES(7239,    9032,    '2' ,      'A');
INSERT INTO t1 VALUES(7239,    9032,    '3' ,      'A');
INSERT INTO t1 VALUES(7239,    9032,    '3.9',     'A');
INSERT INTO t1 VALUES(7239,    9032,    '4'  ,     'A');
INSERT INTO t1 VALUES(7239,    9032,    '4.5',     'A');
INSERT INTO t1 VALUES(7239,    9032,    '5'  ,     'A');
INSERT INTO t1 VALUES(7239,    9032,    '6'  ,     'A');
INSERT INTO t1 VALUES(7239,    9032,    '6.05',    'A');
INSERT INTO t1 VALUES(7239,    9032,    '7.1',     'A');
INSERT INTO t1 VALUES(7239,    9032,    '8'  ,     'A');
INSERT INTO t1 VALUES(7239,    9032,    '9'  ,     'A');
INSERT INTO t1 VALUES(7239,    9032,    '10' ,     'A');
INSERT INTO t1 VALUES(7239,    9032,    '11' ,     'A');
INSERT INTO t1 VALUES(7239,    9032,    '12' ,     'A');
INSERT INTO t1 VALUES(7239,    9032,    '13' ,     'A');
INSERT INTO t1 VALUES(7239,    9032,    '15' ,     'A');
INSERT INTO t1 VALUES(7239,    9032,    '30' ,     'A');
INSERT INTO t1 VALUES(7239,    9199,    '1'  ,     'A');
INSERT INTO t1 VALUES(7239,    9199,    '2'  ,     'A');
INSERT INTO t1 VALUES(7239,    9199,    '3'  ,     'A');
INSERT INTO t1 VALUES(7239,    9199,    '3.9',     'A');
INSERT INTO t1 VALUES(7239,    9199,    '4'  ,     'A');
INSERT INTO t1 VALUES(7239,    9199,    '4.5',     'A');
INSERT INTO t1 VALUES(7239,    9199,    '5'  ,     'A');
INSERT INTO t1 VALUES(7239,    9199,    '6'  ,     'A');
INSERT INTO t1 VALUES(7239,    9199,    '6.05',    'A');
INSERT INTO t1 VALUES(7239,    9199,    '7.1' ,    'B');
INSERT INTO t1 VALUES(7239,    9199,    '8'   ,    'B');
INSERT INTO t1 VALUES(7239,    9199,    '9'   ,    'B');
INSERT INTO t1 VALUES(7239,    9199,    '10'  ,    'B');
INSERT INTO t1 VALUES(7239,    9199,    '11'  ,    'B');
INSERT INTO t1 VALUES(7239,    9199,    '12'  ,    'B');
INSERT INTO t1 VALUES(7239,    9199,    '13'  ,    'A');
INSERT INTO t1 VALUES(7239,    9199,    '15'  ,    'B');
INSERT INTO t1 VALUES(7239,    9199,    '30'  ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '1'   ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '2'   ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '3'   ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '3.9' ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '4'   ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '4.5' ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '5'   ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '6'   ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '6.05',    'A');
INSERT INTO t1 VALUES(7239,    9517,    '7.1' ,    'B');
INSERT INTO t1 VALUES(7239,    9517,    '8'   ,    'C');
INSERT INTO t1 VALUES(7239,    9517,    '9'   ,    'C');
INSERT INTO t1 VALUES(7239,    9517,    '10'  ,    'C');
INSERT INTO t1 VALUES(7239,    9517,    '11'  ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '12'  ,    'C');
INSERT INTO t1 VALUES(7239,    9517,    '13'  ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '15'  ,    'A');
INSERT INTO t1 VALUES(7239,    9517,    '30'  ,    'B');
INSERT INTO t1 VALUES(7239,    10008,   '1'   ,    'A');
INSERT INTO t1 VALUES(7239,    10008,   '2'   ,    'A');
INSERT INTO t1 VALUES(7239,    10008,   '3'   ,    'A');
INSERT INTO t1 VALUES(7239,    10008,   '3.9' ,    'A');
INSERT INTO t1 VALUES(7239,    10008,   '4'   ,    'A');
INSERT INTO t1 VALUES(7239,    10008,   '4.5' ,    'A');
INSERT INTO t1 VALUES(7239,    10008,   '5'   ,    'A');
INSERT INTO t1 VALUES(7239,    10008,   '6'   ,    'A');
INSERT INTO t1 VALUES(7239,    10008,   '6.05',    'A');
INSERT INTO t1 VALUES(7239,    10008,   '7.1' ,    'B');
INSERT INTO t1 VALUES(7239,    10008,   '8'   ,    'C');
INSERT INTO t1 VALUES(7239,    10008,   '9'   ,    'C');
INSERT INTO t1 VALUES(7239,    10008,   '10'  ,    'D');
INSERT INTO t1 VALUES(7239,    10008,   '11'  ,    'B');
INSERT INTO t1 VALUES(7239,    10008,   '12'  ,    'C');
INSERT INTO t1 VALUES(7239,    10008,   '13'  ,    'A');
INSERT INTO t1 VALUES(7239,    10008,   '15'  ,    'B');
INSERT INTO t1 VALUES(7239,    10008,   '30'  ,    'B');