Recursive list of sub products in postgres SQL

Question

I have a quick question concerning Postgres SQL.

My goal is to a product table where we have 1 or more parent products and then certain child products.

In another table sales I just put it the sales of a certain product.

Now to my SQL:

I bascially want to retrieve all sales per superparent product, in my example mobile and home with the sum of sales from the sub products.

My SQL so far:

WITH RECURSIVE assignDepth (id, depth, parent) AS (
  SELECT p.id, 0 as depth, p.parent
  FROM product p
  WHERE p.parent IS NULL
  UNION ALL
  SELECT p.id, c.depth+1, p.parent
  FROM product p, assignDepth c
  WHERE p.parent = c.id
 ),
  getProductValue (id, name, value, parent) AS (
   SELECT p.id, p.name, 0 as value, p.parent
   FROM product p,
   WHERE p.depth = (SELECT MAX(depth) FROM assignDepth)
   UNION ALL
         SELECT p.id, p.name,SUM(
             SELECT s.value
             FROM sales s
             WHERE c.id = s.product), p.parent
   FROM product p, getProductValue c
   WHERE p.id = c.parent
 )
SELECT * FROM getProductValue;

Thanks in advance, any help is appreciated! :)

Answer 1

I solved it by myself with only one recursion.

WITH RECURSIVE getProductValue (i, n, par) AS 
(SELECT id as i, name as n, id as par FROM product
  UNION all 
        SELECT product.id AS i, product.name AS n, getProductValue.par 
        FROM product, getProductValue  
        WHERE product.parent=getProductValue.i)
SELECT par, SUM(value) 
FROM getProductValue, sales 
WHERE par<>i 
AND sales.product=getProductValue.i 
GROUP by par;

That's the solution if anyone is interested!