The result of the recursive query statement is not what I want

[ximen]

I want to use recursive query to achieve the total number, but the existing statement can only count the ID of the first layer:

CREATE TABLE "DBA"."sumall" (
    "ID" CHAR(5) NOT NULL,
    "NUM_one" NUMERIC(6,2) NULL,
    "NUM_TWO" NUMERIC(6,2) NULL,
    "parent_ID" CHAR(5) NULL,
    CONSTRAINT "ID" PRIMARY KEY ( "ID" ASC )
) IN "system";
COMMENT ON TABLE "DBA"."sumall" IS '
';


    INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('1011',2.00,11.00,NULL);
    INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('1021',2.00,33.00,NULL);
    INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('1033',23.00,332.00,'1021');
    INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('1044',2.00,2.00,'1011');
    INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('10881',23.00,2333.00,'1033');
    INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('4566',1.00,2.00,'1033');
    INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('7788',2.00,1.00,'4566');

---

SQL:

WITH RECURSIVE  sumid (d1,dd,sum_one,sum_two,)
as (

select id as d1,id as dd  , num_one   , num_two  two   from sumall where sumall.id in (select DISTINCT  parent_id  from sumall where isnull(parent_id,'') <> '')

union all
select sumid.dd,sumall.id,num_one ,num_two  from sumall,sumid where sumid.dd=sumall.parent_id)

 select * from sumid

Use recursive query and total quantity：

[Breck_Carter]

Maybe the second example in this blog post will help: Example: RECURSIVE UNION.

[ximen]

It looks like mine is a counterclockwise recursion, I can't get it out!Please help me look at my problem

[ximen]

The answer is as follows：

WITH RECURSIVE  sumid (d1,dd,sum_one,sum_two,)
    as (

    select id as d1,id  dd , num_one   , num_two  two   from sumall where sumall.id in (select DISTINCT  parent_id  from sumall where isnull(parent_id,'') <> '')

    union all
    select sumall.ID,sumid.dd,num_one ,num_two  from sumall,sumid where sumid.d1=sumall.parent_id)




     select dd,sum(sum_one),sum(sum_two) from sumid group by dd

Thanks for Breck Carter and sybase forum

result:

dd,sum(sumid.sum_one),sum(sumid.sum_two)
'1011',4.000000,13.000000
'1021',51.000000,2701.000000
'1033',49.000000,2668.000000
'4566',3.000000,3.000000