Select multiple columns from subquery - Connect by

Question

I have a table which manages folder structure. Hence it has a parent-child relationship. We trigger a query to sum all documents in the complete heirarchy:

 SELECT folder_name name,
 document_count,
 (SELECT SUM(e2.document_count) 
 FROM folder_mapping e2 START WITH e2.folder_mapping_id = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
 )
 sum_sal
 FROM
 folder_mapping e1

Now I have a requirement to calculate the sum of one more column

 SELECT folder_name name,
 document_count,
 (SELECT SUM(e2.document_count), **sum(e2.folder_count)**
 FROM folder_mapping e2 START WITH e2.folder_mapping_id = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
 )
 sum_sal
 FROM
 folder_mapping e1

This query throws error:>ORA-00913: too many values.

But what can be done to achieve the same?

Database structure with data:
enter image description here

Intended output:
enter image description here

Query with help of JSapkota

SELECT
 fm.folder_mapping_id,
 fm_main.folder_name,
 fm.Total_Doc_Count,
 fm.Total_Folder_Count,
 -- fm.shared_flag
 DECODE(fm.shared_flag, fm_main.shared_flag
 ||'', (DECODE(fm.shared_flag, '0', 'Not Shared', '1', 'Fully Shared',
 'Partially Shared')), 'Partially Shared') shared_flag
FROM
 (
 SELECT
 folder_mapping_id,
 SUM(document_count) Total_Doc_Count,
 SUM(folder_count) Total_Folder_Count,
 SUM(SHARED_FLAG) SHARED_FLAG,
 SUM(attachment_flag) attachment_flag
 FROM
 (
 SELECT
 CONNECT_BY_ROOT folder_mapping_id AS folder_mapping_id,
 document_count,
 folder_count,
 SHARED_FLAG,
 attachment_flag
 FROM
 (
 SELECT
 *
 FROM
 folder_mapping
 WHERE
 organization_id='Org_498'
 )
 --where organization_id='Org_498'
 CONNECT BY PRIOR folder_mapping_id = parent_folder_mapping_id
 )
 GROUP BY
 folder_mapping_id
 )
 fm,
 (
 SELECT
 folder_mapping_id,
 document_count,
 folder_name,
 folder_count,
 SHARED_FLAG
 FROM
 folder_mapping
 WHERE
 organization_id='Org_498'
 )
 fm_main
WHERE
 fm_main.folder_mapping_id = fm.folder_mapping_id
ORDER BY
 fm.folder_mapping_id;

With input as follows: enter image description here

Current output is: enter image description here

The output is pretty much OK except the fact that we want root9 to be displayed as fully shared since it is a subfolder of root8 which is fully shared.

Convention followed for shared flag
--0 - not shared
--1 - fully shared
--2 - partially shared

In case a folder is fully shared its subfolders are fully shared as well.

In case a folder is partially shared or fully shared its parent is partially shared.

Insert queries:

Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (1,'root1','111',0,2,3,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (2,'root2','111',1,2,33,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (3,'root3','111',1,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (4,'root4','111',2,2,2,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (5,'root5','111',3,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (6,'root6','111',5,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (7,'root7','111',6,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (8,'root8','111',7,2,1,'1','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (9,'root9','111',8,2,1,'0','1','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');

• Varun: Added the data, yes first query is working.. second query is not working ORA-00913: too many values

Answer 1

ORA-00913 too many values

Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT.
Action: Check the number of items in each set and change the SQL statement to make them equal.

In your case your subquery is returning two columns to the first query expecting one column. You can change your query as follow.

SELECT folder_name name,
 document_count,
 (SELECT SUM(e2.document_count)
 FROM folder_mapping e2 START WITH e2.folder_mapping_id = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
 )
 sum_sal, (SELECT SUM(e2.document_count)
 FROM folder_mapping e2 START WITH e2.folder_mapping_id = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
 ) folder_count_total
FROM
 folder_mapping e1

That was just the fix for your error. Actually you can use the following query to get the desired output which is better(In terms of cost) than the original query.

SQL> desc folder_mapping;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 FOLDER_MAPPING_ID NUMBER
 FOLDER_NAME VARCHAR2(20)
 FOLDER_FILENET_ID NUMBER
 PARENT_FOLDER_MAPPING_ID NUMBER
 FOLDER_COUNT NUMBER
 DOCUMENT_COUNT NUMBER
 SHARED_FLAG NUMBER
 ATTACHMENT_FLAG NUMBER
SQL> select * from folder_mapping;
FOLDER_MAPPING_ID FOLDER_NAME FOLDER_FILENET_ID
----------------- -------------------- -----------------
PARENT_FOLDER_MAPPING_ID FOLDER_COUNT DOCUMENT_COUNT SHARED_FLAG ATTACHMENT_FLAG
------------------------ ------------ -------------- ----------- ---------------
 4 root3 111
 2 2 2 0 0
 1 root 11
 0 2 3 0 0
 2 root1 111
 1 2 33 0 0
SQL> SELECT folder_name, SUM(document_count) "Total_Doc_Count", SUM(folder_count) "Total_Folder_Count" FROM(
 SELECT CONNECT_BY_ROOT folder_name as folder_name, document_count, folder_count
 FROM folder_mapping
 CONNECT BY PRIOR folder_mapping_id = parent_folder_mapping_id)
 GROUP BY folder_name; 
FOLDER_NAME Total_Doc_Count Total_Folder_Count
-------------------- --------------- ------------------
root1 35 4
root 38 6
root3 2 2
SQL> 

• Varun: JSapkota - this will be really heavy option to perform we would like something which is cost effective
• Varun: actual requirement is even vast and needs to make some checks on all the columns this is a small portion of actual requirement.. So the solution i am seeking should be extensible enough to perform some actions based on level of folder in heirarchy
• atokpas: @Varun Please notice the edit for the better query prepared according to the information provided.
• Varun: the output seems good, great effort thanks alot :)
• Varun: can something be done to achieve bottom up approach as well, please check the updated question