Complex (at least for me) sql question

hi everyone! :+1: i use postgresql 10, but if the solution is proposed in pure sql, i might also find it useful.
i have the table that you see in the attached image. the goal is to obtain the values of each element starting from the bottom up, maybe using a tree structure. the result of the lower level serves to feed the upper level. i’m lost here.

below the sql needed to generate the table

/* CREATE TABLE */
CREATE TABLE table_name(
id_father VARCHAR(100),
id_son VARCHAR(100),
qty DOUBLE PRECISION,
price DOUBLE PRECISION,
subtotal DOUBLE PRECISION,
vuelta DOUBLE PRECISION
);

/* INSERT VALUES */
INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('ACC001', 'AF3000', 1, 8192.97, 8192.97, 3);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('ACC001', 'C3000', 2.31, 4179.32, 9654.2292, 3);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('ACC001', 'E3000', 8.2, 1135.79, 9313.478, 3);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('BAE3000L', 'AD006', 6.01, 2.4642, 14.809842, 3);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('BAE3000L', 'ELPIE012', 1.84, 1462.31, 2690.6504, 3);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('BAE3000L', 'HOCHAC013', 2.332, 218.559, 509.679588, 3);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('BAE3000LEQUIP', 'CUPIE010', 5.8, 132.77, 770.066, 3);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('BAE3000LEQUIP', 'GS002', 5.001, 924.4133, 4622.9909133, 3);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('BAE3000LEQUIP', 'PLCAN001', 5.1, 71.92, 366.792, 3);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('BETAX', 'ACC001', 1, null, null, 2);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('BETAX', 'BAE3000L', 1, null, null, 2);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('BETAX', 'BAE3000LEQUIP', 1, null, null, 2);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('BETAX', 'TUERQUITA001', 23.7, 10, 237, 2);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('ALFA001', 'BETAX', 2, null, null, 1);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('ALFA001', 'TUERQUITA001', 3.33, 10, 33.3, 1);

INSERT INTO table_name(id_father, id_son, qty, price, subtotal, vuelta)
VALUES('ALFA001', 'ALFA001', null, null, null, 0);

i appreciate any help you can give me.

wow… is it really that difficult?

wow… is it really that difficult?

It is if you try to do it all in one SQL statement.

But use baby steps…

Make a view selecting id_father, id_son, qty, price, price * qty

Then a view using that view, grouped by id_father, using 1 as the qty, sum(price) as price and sum (subtotal) as subtotal

Then a view using that view, and so on

After 3 or 4 levels, you have a set of data that you can place in a table like this.

You can select from all the views at once using UNION SELECT ALL

1 Like

I reread your question several times and I still don’t know what you’re looking for. Perhaps if you showed us the results you are looking for?

1 Like

I think what you are looking for us recursion:

1 Like