Select multiple columns from subquery - Connect by

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:

Intended output:

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'

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>

StackExchange Database Administrators Q#137336, answer score: 4