MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.36


CREATE TABLE tenant_category_transaction_view (
    id               int primary key
  , idParentCategory int
  , sumSubtotal      int
  , sumQuantity      int
);

INSERT INTO tenant_category_transaction_view VALUES
    (1, null, 9800, 98)
  , (4, null,   20,  1)
  , (5,    4,   30,  1)
  , (6,    5,   40,  1)
;

Records: 4  Duplicates: 0  Warnings: 0


WITH RECURSIVE nodes AS (
         SELECT id, COALESCE(idParentCategory, 0) AS idParentCategory
              , sumSubtotal, sumQuantity
           FROM tenant_category_transaction_view
          UNION
         SELECT 0, null, 0, 0
     )
   , cte AS (
        SELECT t.*, t.id AS root
             , idParentCategory AS idParentCategory0
             , sumSubtotal      AS sumSubtotal0
             , sumQuantity      AS sumQuantity0
          FROM nodes AS t
         UNION ALL
        SELECT t.* , t0.root
             , t0.idParentCategory0
             , t0.sumSubtotal0
             , t0.sumQuantity0
          FROM cte AS t0
          JOIN nodes AS t
            ON t.idParentCategory = t0.id
     )
SELECT root
     , MIN(idParentCategory0)   AS idParentCategory
     , MIN(sumSubtotal0)        AS sumSubtotal
     , MIN(sumQuantity0)        AS sumQuantity
     , SUM(t1.sumSubtotal)      AS total
  FROM cte AS t1
 GROUP BY root
 ORDER BY root
;


root
idParentCategory
sumSubtotal
sumQuantity
total


0
null
0
0
9890

1
0
9800
98
9800

4
0
20
1
90

5
4
30
1
70

6
5
40
1
40


with recursive cte (sumSubtotal, sumQuantity, id, idParentCategory, treeSum, depth) as (

        select  root.sumSubtotal, -- STEP 1
                root.sumQuantity, 
                root.id, 
                root.idParentCategory, 
                root.sumSubtotal as treeSum,
                0 as depth
        from    tenant_category_transaction_view as root

        union all -- LOOP THROUGH ALL ROOT ROWS AND ADD ROWS TO THE CTE WITH THE INNER JOIN

        select  child.sumSubtotal, -- STEP 3
                child.sumQuantity, 
                child.id, 
                child.idParentCategory, 
                (cte.treeSum + child.sumSubtotal) AS treeSum,
                (cte.depth + 1) AS depth
        from    tenant_category_transaction_view AS child

        inner join cte on child.idParentCategory = cte.id -- STEP 2
)
select sumSubtotal, sumQuantity, id, idParentCategory, treeSum, depth -- STEP 4
from cte ORDER BY depth, id
;


sumSubtotal
sumQuantity
id
idParentCategory
treeSum
depth


9800
98
1
null
9800
0

20
1
4
null
20
0

30
1
5
4
30
0

40
1
6
5
40
0

30
1
5
4
50
1

40
1
6
5
70
1

40
1
6
5
90
2


DROP TABLE IF EXISTS tenant_category_transaction_view;
CREATE TABLE tenant_category_transaction_view (
    id               int primary key
  , idParentCategory int
  , sumSubtotal      int
  , sumQuantity      int
);

INSERT INTO tenant_category_transaction_view VALUES
    (99, null,    0,  0)
  , ( 1,   99, 9800, 98)
  , ( 4,   99,   20,  1)
  , ( 5,    4,   30,  1)
  , ( 6,    5,   40,  1)
;

Records: 5  Duplicates: 0  Warnings: 0


WITH RECURSIVE cte AS (
        SELECT t.*, t.id AS root
          FROM tenant_category_transaction_view AS t
         UNION ALL
        SELECT t.*, t0.root
          FROM cte AS t0
          JOIN tenant_category_transaction_view AS t
            ON t.idParentCategory = t0.id
     )
SELECT root
     , MIN(t2.idParentCategory) AS idParentCategory
     , MIN(t2.sumSubtotal)      AS sumSubtotal
     , MIN(t2.sumQuantity)      AS sumQuantity
     , SUM(t1.sumSubtotal)      AS total
  FROM cte AS t1
  JOIN tenant_category_transaction_view AS t2
    ON t1.root = t2.id
 GROUP BY root
 ORDER BY root
;


root
idParentCategory
sumSubtotal
sumQuantity
total


1
99
9800
98
9800

4
99
20
1
90

5
4
30
1
70

6
5
40
1
40

99
null
0
0
9890


WITH RECURSIVE cte AS (
        SELECT t.*, t.id AS root
          FROM tenant_category_transaction_view AS t
         UNION ALL
        SELECT t.*, t0.root
          FROM cte AS t0
          JOIN tenant_category_transaction_view AS t
            ON t.idParentCategory = t0.id
     )
SELECT t2.id
     , t2.idParentCategory
     , t2.sumSubtotal
     , t2.sumQuantity
     , SUM(t1.sumSubtotal)      AS total
  FROM cte AS t1
  JOIN tenant_category_transaction_view AS t2
    ON t1.root = t2.id
 GROUP BY t2.id
 ORDER BY t2.id
;


id
idParentCategory
sumSubtotal
sumQuantity
total


1
99
9800
98
9800

4
99
20
1
90

5
4
30
1
70

6
5
40
1
40

99
null
0
0
9890


WITH RECURSIVE cte AS (
        SELECT t.*, t.id AS root
             , idParentCategory AS idParentCategory0
             , sumSubtotal      AS sumSubtotal0
             , sumQuantity      AS sumQuantity0
          FROM tenant_category_transaction_view AS t
         UNION ALL
        SELECT t.* , t0.root
             , t0.idParentCategory0
             , t0.sumSubtotal0
             , t0.sumQuantity0
          FROM cte AS t0
          JOIN tenant_category_transaction_view AS t
            ON t.idParentCategory = t0.id
     )
SELECT root
     , MIN(idParentCategory0)   AS idParentCategory
     , MIN(sumSubtotal0)        AS sumSubtotal
     , MIN(sumQuantity0)        AS sumQuantity
     , SUM(t1.sumSubtotal)      AS total
  FROM cte AS t1
 GROUP BY root
 ORDER BY root
;


root
idParentCategory
sumSubtotal
sumQuantity
total


1
99
9800
98
9800

4
99
20
1
90

5
4
30
1
70

6
5
40
1
40

99
null
0
0
9890
sumSubtotal	sumQuantity	id	idParentCategory	treeSum	depth
9800	98	1	null	9800	0
20	1	4	null	20	0
30	1	5	4	30	0
40	1	6	5	40	0
30	1	5	4	50	1
40	1	6	5	70	1
40	1	6	5	90	2