Sqlite recursive query

Question

I’m starting with sqlite and would like help with a recursive query.

I have 3 tables (generic example):

CREATE TABLE IF NOT EXISTS tab_componente (
        id_comp TEXT NOT NULL PRIMARY KEY,
        desc_comp TEXT ,
        custo_comp INTEGER);

CREATE TABLE IF NOT EXISTS tab_montagem (
        id_mont TEXT NOT NULL PRIMARY KEY,
        desc_mont TEXT);

CREATE TABLE IF NOT EXISTS tab_mont_itens (
        id_item TEXT NOT NULL,
        id_parent TEXT NOT NULL,
        tipo TEXT,
        quant INTEGER NOT NULL);

INSERT INTO tab_componente(id_comp, desc_comp, custo_comp)
VALUES ('comp1', 'componente 1', 1.00), 
       ('comp2', 'componente 2', 1.00), 
       ('comp3', 'componente 3', 1.00), 
       ('comp4', 'componente 4', 1.00), 
       ('comp5', 'componente 5', 1.00), 
       ('comp6', 'componente 6', 1.00),
       ('comp7', 'componente 7', 2.00), 
       ('comp8', 'componente 8', 1.00);

INSERT INTO tab_montagem(id_mont, desc_mont)
VALUES ('mont1', 'montagem 1'), 
       ('mont2', 'montagem 2'), 
       ('mont3', 'montagem 3'), 
       ('mont4', 'montagem 4'), 
       ('mont5', 'montagem 5');

INSERT INTO tab_mont_itens (id_item, id_parent, tipo, quant)  
VALUES
('comp1', 'mont1', 'componente' ,1.00),
('comp2', 'mont1', 'componente', 1.00),
('comp3', 'mont1', 'componente', 1.00),

('comp2', 'mont2', 'componente', 1.00),

('comp4', 'mont3', 'componente' ,1.00),
('comp5', 'mont3', 'componente', 1.00),
('comp7', 'mont3', 'componente', 1.00), 

('comp6', 'mont4', 'componente', 1.00),
('comp8', 'mont4', 'componente', 1.00),

('comp7', 'mont5', 'componente' ,1.00),      

('mont4', 'mont2', 'montagem', 2.00), 
('mont4', 'mont3', 'montagem', 3.00),
('mont5', 'mont2', 'montagem', 1.00),
('mont5', 'mont3', 'montagem', 1.00);

It is a tree structure with mounts and sub-mounts:

• mont1

  • comp1

  • comp2

  • comp3

• mont2

  • comp2

  • mont4 (Want = 2)

    • comp6

    • comp8

  • mont5

    • comp7

• mont3

  • comp4

  • comp5

  • comp7

  • mont4 (Want = 3)

    • comp6

    • comp8

  • mont5

    • comp7

The recursive query for a specific mount works correctly showing the sum of components and sub-components, for example:

WITH RECURSIVE descendentes AS 
(SELECT id_parent AS parent, id_item AS descendente, quant, tipo FROM tab_mont_itens
UNION ALL 
SELECT d.parent, p.id_item, p.quant*d.quant, p.tipo
FROM descendentes AS d
INNER JOIN tab_mont_itens AS p
ON d.descendente = p.id_parent)
SELECT parent,descendente, SUM(quant), tipo FROM descendentes
WHERE parent = 'mont2' AND tipo = 'componente'
GROUP BY descendente

But my goal is to show the total cost per component, with all the assemblies, so I tried as follows:

SELECT id_comp,custo_comp, t.soma_comp,(custo_comp*t.soma_comp) AS custo FROM tab_componente
INNER JOIN
(WITH RECURSIVE descendentes AS (
SELECT id_parent AS parent, id_item AS descendente, quant, tipo FROM tab_mont_itens
UNION ALL 
SELECT d.parent, p.id_item, p.quant*d.quant, p.tipo
FROM descendentes AS d
INNER JOIN tab_mont_itens AS p
ON d.descendente = p.id_parent)
SELECT descendente, SUM(quant) AS soma_comp, tipo FROM descendentes
WHERE tipo = 'componente'
GROUP BY descendente) AS t ON id_comp = t.descendente
GROUP BY id_comp

But the result shows the correct sums of components 1 to 5 and components 6, 7 and 8 with wrong values (adding +1 ?).

Can someone tell me what I’m doing wrong, or give me a suggestion on how to do it otherwise?