title: Complex nested aggregations to get order totals
body: <p>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.</p>
  
  <p>The <code>expenditures</code> table look like this:</p>
  
  <pre><code>+----+----------+-----------+------------------------+
  | 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    |
  +----+----------+-----------+------------------------+
  </code></pre>
  
  <p>Each <code>expenditure</code> has many <code>expenditure_items</code> and can the orders can be parents to the invoices.  That table looks like this:</p>
  
  <pre><code>+----+----------------+-------------+-------+---------+
  | 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 |
  +----+----------------+-------------+-------+---------+
  </code></pre>
  
  <p>I need to track a few things:</p>
  
  <ul>
  <li>amounts left to be invoiced on orders (thats easy)</li>
  <li>above but rolled up for each <code>cbs_item_id</code> (this is the ugly part)</li>
  </ul>
  
  <p>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:</p>
  
  <pre><code>+-------------+----------------+-------------+---------------------------+-----------+
  | 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       |
  +-------------+----------------+-------------+---------------------------+-----------+
  </code></pre>
  
  <p><code>order_total</code> is the sum of <code>total</code> for all the expenditure_items of the given order ( category = 'order').  <code>invoice_total</code> 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 (<code>cbs_item_id</code>).  Then I need the roll-up of all the remaining values grouped by the <code>cbs_item_id</code>.</p>
  
  <p>So for each <code>cbs_item_id</code> 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.</p>
  
  <p>In the end looking for a result something like this:</p>
  
  <pre><code>+-------------+-----------+
  | cbs_item_id | remaining |
  +-------------+-----------+
  | 1           | 68        |
  +-------------+-----------+
  | 2           | 297       |
  +-------------+-----------+
  </code></pre>
  
  <p>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.</p>
  
  <p>Here is a fiddle for the data above:</p>
  
  <p><a href="http://sqlfiddle.com/#!17/2fe3a" rel="nofollow noreferrer">http://sqlfiddle.com/#!17/2fe3a</a></p>
  
  <p>Alternate fiddle:</p>
  
  <p><a href="https://dbfiddle.uk/?rdbms=postgres_11&amp;fiddle=e9528042874206477efbe0f0e86326fb" rel="nofollow noreferrer">https://dbfiddle.uk/?rdbms=postgres_11&amp;fiddle=e9528042874206477efbe0f0e86326fb</a></p>