Hallo Andreas
Ich bekomme jetzt Zahlen, die sehen auch nicht mal so schlecht aus. Vor allem habe ich nur noch root Kategorien die auch was verkauft haben.
Aber die Summen scheinen nicht zu stimmen. Ich habe mal exemplarisch ein Artikel, der nur 1 Level Unterartikel hat, nachgerechnet.
SELECT
SUM(l.unit_price * l.quantity) AS revenue
FROM account_invoice_line l
JOIN account_invoice i ON i.id = l.invoice
JOIN product_product p ON p.id = l.product
JOIN product_template t ON t.id = p.template
JOIN product_category c ON c.id = t.category
WHERE l.invoice_type = ‘out_invoice’
AND i.state IN (‘open’, ‘paid’)
AND c.id = 40
AND c.parent is null
OR
l.invoice_type = ‘out_invoice’
AND i.state IN (‘open’, ‘paid’)
AND c.parent = 40
Hier ergibt die Summe 46959.5.
Wenn ich das rekursive Query laufen lasse bekomme ich auf dem Artikel 40 eine Summe 140872.5. Das sind ziemlich genau drei mal mehr.
Beim Artikel 25 stimmt es aber aufs Loch. Das verwirrt mich jetzt ein wenig. 
Mein Query:
select
id,
0 as create_uid,
current_timestamp as create_date,
0 as write_uid,
current_timestamp AS write_date,
id AS product_category,
sum(revenue) as revenue
from (
with recursive r as (
SELECT
row_number() over () as root_number,
c.id AS product_category,
l.unit_price * l.quantity AS revenue,
c.id AS id,
c.parent as parent
FROM account_invoice_line l
JOIN account_invoice i ON i.id = l.invoice
JOIN product_product p ON p.id = l.product
JOIN product_template t ON t.id = p.template
JOIN product_category c ON c.id = t.category
WHERE l.invoice_type = ‘out_invoice’
AND c.parent IS NULL
AND i.state IN (‘open’, ‘paid’)
union all
SELECT
r.root_number,
c.id AS product_category,
l.unit_price * l.quantity AS revenue,
c.id AS id,
c.parent as parent
FROM account_invoice_line l
JOIN account_invoice i ON i.id = l.invoice
JOIN product_product p ON p.id = l.product
JOIN product_template t ON t.id = p.template
JOIN product_category c ON c.id = t.category
INNER JOIN r on (c.parent = r.id)
WHERE l.invoice_type = ‘out_invoice’
AND i.state IN (‘open’, ‘paid’)
), n as (
select id, root_number
from r where parent is null
)
select r.root_number, r.revenue, n.id
from r
left join n on r.root_number=n.root_number
) foo group by id
ORDER BY id