Complex nested aggregations to get order totals

Question

I have a system to track orders and related expenditures. This is a Rails app running on PostgreSQL. 99% of my app gets by with plain old Rails Active Record call etc. This one is ugly.

The expenditures table look like this:

+----+----------+-----------+------------------------+
| id | category | parent_id | note                   |
+----+----------+-----------+------------------------+
| 1  | order    | nil       | order with no invoices |
+----+----------+-----------+------------------------+
| 2  | order    | nil       | order with invoices    |
+----+----------+-----------+------------------------+
| 3  | invoice  | 2         | invoice for order 2    |
+----+----------+-----------+------------------------+
| 4  | invoice  | 2         | invoice for order 2    |
+----+----------+-----------+------------------------+

Each expenditure has many expenditure_items and can the orders can be parents to the invoices. That table looks like this:

+----+----------------+-------------+-------+---------+
| id | expenditure_id | cbs_item_id | total | note    |
+----+----------------+-------------+-------+---------+
| 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 |
+----+----------------+-------------+-------+---------+

I need to track a few things:

• amounts left to be invoiced on orders (thats easy)
• above but rolled up for each cbs_item_id (this is the ugly part)

The cbs_item_id is basically an accounting code to categorize the money spent etc. I have visualized what my end result would look like:

+-------------+----------------+-------------+---------------------------+-----------+
| cbs_item_id | expenditure_id | order_total | invoice_total             | remaining |
+-------------+----------------+-------------+---------------------------+-----------+
| 1           | 1              | 5           | 0                         | 5         |
+-------------+----------------+-------------+---------------------------+-----------+
| 1           | 2              | 123         | 60                        | 63        |
+-------------+----------------+-------------+---------------------------+-----------+
|             |                |             | Rollup for cbs_item_id: 1 | 68        |
+-------------+----------------+-------------+---------------------------+-----------+
| 2           | 1              | 15          | 0                         | 15        |
+-------------+----------------+-------------+---------------------------+-----------+
| 2           | 2              | 456         | 174                       | 282       |
+-------------+----------------+-------------+---------------------------+-----------+
|             |                |             | Rollup for cbs_item_id: 2 | 297       |
+-------------+----------------+-------------+---------------------------+-----------+

order_total is the sum of total for all the expenditure_items of the given order ( category = 'order'). invoice_total is the sum of total for all the expenditure_items with parent_id = expenditures.id. Remaining is calculated as the difference (but not greater than 0). In real terms the idea here is you place and order for $1000 and $750 of invoices come in. I need to calculate that $250 left on the order (remaining) - broken down into each category (cbs_item_id). Then I need the roll-up of all the remaining values grouped by the cbs_item_id.

So for each cbs_item_id I need group by each order, find the total for the order, find the total invoiced against the order then subtract the two (also can't be negative). It has to be on a per order basis - the overall aggregate difference will not return the expected results.

In the end looking for a result something like this:

+-------------+-----------+
| cbs_item_id | remaining |
+-------------+-----------+
| 1           | 68        |
+-------------+-----------+
| 2           | 297       |
+-------------+-----------+

I am guessing this might be a combination of GROUP BY and perhaps a sub query or even CTE (voodoo to me). My SQL skills are not that great and this is WAY above my pay grade.

Here is a fiddle for the data above:

http://sqlfiddle.com/#!17/2fe3a

Alternate fiddle:

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e9528042874206477efbe0f0e86326fb

Answer 1

This query produces the result you are looking for:

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;

db<>fiddle here

① Note how I assume a saner table definition with expenditures.parent_id being integer, and true NULL instead of the string 'nil'. This allows the simple use of COALESCE.

② About the aggregate FILTER clause:

• Aggregate columns with additional (distinct) filters

③ Using short syntax with ordinal numbers of an SELECT list items. Example:

• Select first row in each GROUP BY group?

can I get the total of all the remaining for all rows or do I need to wrap that into another sub select?

There is a very concise option with GROUPING SETS:

...
GROUP  BY GROUPING SETS ((1), ())  -- that's all :)

db<>fiddle here

Related:

• Converting rows to columns