Forum Stats

  • 3,734,268 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

Sql with hierarchical tree view with count rolloup option

ORA-00904
ORA-00904 Member Posts: 33
edited May 29, 2015 7:42AM in SQL

How would I write SQL to return tree view with rolloup count:

SQL would return this:

KLAS - COUNT
----------------------------------------------
ROOT - 10
   KLAS1 - 5
       KLAS2 - 2
       KLAS3 - 3
  KLAS4 - 5
       KLAS5 - 5
  KLAS6 - 0
       KLAS7 - 0

I have two tables, one is where is structred hold, second is where I have count. Both tables are joined by themselves by klas coumn

    Code for reproduce tables:

create table table1 (id number, parent_id number, klas varchar2(10));
insert into table1 (id, parent_id, klas) values (1, null, 'ROOT');
insert into table1 (id, parent_id, klas) values (2, 1, 'KLAS1');
insert into table1 (id, parent_id, klas) values (3, 2, 'KLAS2');
insert into table1 (id, parent_id, klas) values (4, 2, 'KLAS3');
insert into table1 (id, parent_id, klas) values (5, 1, 'KLAS4');
insert into table1 (id, parent_id, klas) values (6, 5, 'KLAS5');
insert into table1 (id, parent_id, klas) values (7, 1, 'KLAS6');
insert into table1 (id, parent_id, klas) values (8, 7, 'KLAS7');



create table table2 (klas varchar2(10), cnt number);
insert into table2(klas, cnt) values ('KLAS2', 2);
insert into table2(klas, cnt) values ('KLAS3', 3);
insert into table2(klas, cnt) values ('KLAS5', 5);

commit;

I would like sql only returns rows with count > 0..

Regards,

Igor

Sign In or Register to comment.