Nested self join and creating multiple sums

I have a basic parent / child scheme for expenditures:

The underling data is the same so I just added a category column and parent_id. These have child records:

I am trying to aggregate the totals form the orders, related orders and difference between the two like this:

Which is grouped by the orders overall then I am also looking for something like this:

I can get the order_amount no problem either way. That’s a simple JOIN and SUM.

I am stuck on the secondary JOINS given that I have to JOIN the invoices expenditures to the orders then JOIN the invoice expenditure items and SUM that up.

I am looking for direction on the correct JOIN or if there is a better way to approach this with some sort of subquery etc.

Answer

To sump up by order, one solution would be to use a conditional aggregate query. A trick is to check the category to decide whether to use the value from column expenditures.id or from column expenditures.parent_id as grouping criteria:

SELECT
    CASE WHEN e.category = 'order' THEN e.id ELSE e.parent_id END expenditure_id,
    SUM(CASE WHEN e.category = 'order' THEN i.amount ELSE 0 END) order_amount,
    SUM(CASE WHEN e.category = 'invoice' THEN i.amount ELSE 0 END) order_amount,
    SUM(CASE WHEN e.category = 'order' THEN i.amount ELSE 0 END)
        - SUM(CASE WHEN e.category = 'invoice' THEN i.amount ELSE 0 END) balance
FROM expenditures e
LEFT JOIN expenditure_items i ON e.id = i.expenditure_id
GROUP BY CASE WHEN e.category = 'order' THEN e.id ELSE e.parent_id END
ORDER BY expenditure_id

Demo on DB Fiddle:

| expenditure_id | order_amount | order_amount | balance |
| -------------- | ------------ | ------------ | ------- |
| 1              | 3740         | 0            | 3740    |
| 2              | 11000        | 9350         | 1650    |

---

The second query, that sums up by item code, basically follows the same logic, but groups by idem code instead:

SELECT
    i.code,
    SUM(CASE WHEN e.category = 'order' THEN i.amount ELSE 0 END) order_amount,
    SUM(CASE WHEN e.category = 'invoice' THEN i.amount ELSE 0 END) order_amount,
    SUM(CASE WHEN e.category = 'order' THEN i.amount ELSE 0 END)
        - SUM(CASE WHEN e.category = 'invoice' THEN i.amount ELSE 0 END) balance
FROM expenditures e
LEFT JOIN expenditure_items i ON e.id = i.expenditure_id
GROUP BY i.code
ORDER BY i.code;

Demo:

| code | order_amount | order_amount | balance |
| ---- | ------------ | ------------ | ------- |
| a    | 13400        | 8500         | 4900    |
| b    | 1340         | 850          | 490     |