Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE expenditures (id int, category text, parent_id int, note text);
INSERT INTO expenditures VALUES
   (1, 'order', null, 'order with no invoices'),
   (2, 'order', null, 'order with invoices'),
   (3, 'invoice',  2, 'invoice for order 2'),
   (4, 'invoice',  2, 'invoice for order 2');

CREATE TABLE expenditure_items (id int, expenditure_id int, cbs_item_id int, total int, note text);
INSERT INTO expenditure_items VALUES
   (1, 1, 1,   5, 'Fuit'),
   (2, 1, 2,  15, 'Veggies'),
   (3, 2, 1, 123, 'Fuit'),
   (4, 2, 2, 456, 'Veggies'),
   (5, 3, 1,  34, 'Fuit'),
   (6, 3, 2,  76, 'Veggies'),
   (7, 4, 1,  26, 'Fuit'),
   (8, 4, 2,  98, 'Veggies');
4 rows affected

8 rows affected

SELECT cbs_item_id, sum(order_total - invoice_total) AS remaining
FROM  (
   SELECT cbs_item_id
        , COALESCE(e.parent_id, e.id) AS expenditure_id
        , COALESCE(sum(total) FILTER (WHERE e.category = 'order'  ), 0) AS order_total
        , COALESCE(sum(total) FILTER (WHERE e.category = 'invoice'), 0) AS invoice_total
   FROM   expenditures      e
   JOIN   expenditure_items i ON i.expenditure_id = e.id
   GROUP  BY 1, 2
   ) sub
GROUP  BY 1
ORDER  BY 1;


cbs_item_id
remaining


1
68

2
297


-- plus total remaining
SELECT cbs_item_id, sum(order_total - invoice_total) AS remaining
FROM  (
   SELECT cbs_item_id
        , COALESCE(e.parent_id, e.id) AS expenditure_id
        , COALESCE(sum(total) FILTER (WHERE e.category = 'order'  ), 0) AS order_total
        , COALESCE(sum(total) FILTER (WHERE e.category = 'invoice'), 0) AS invoice_total
   FROM   expenditures      e
   JOIN   expenditure_items i ON i.expenditure_id = e.id
   GROUP  BY 1, 2
   ) sub
GROUP  BY GROUPING SETS ((1), ())  -- that's it :)
ORDER  BY 1;


cbs_item_id
remaining


1
68

2
297

null
365