title: MySQL sum amounts for transactions related to loans, in reverse order
body: <p>I'm trying to add an amount living in a transactions table, but the adding must be done taking the transactions in reversed order, grouped by a loan they belong to. Let me put some examples:</p>
  
  <pre><code>Transactions
  tid    loanid    amount    entrydate
  ------------------------------------
  1      1         1,500     2013-06-01
  2      2         1,500     2013-06-01
  3      1         1,000     2013-06-02
  4      3         2,300     2013-06-04
  5      5         2,000     2013-06-04
  6      1         1,100     2013-06-07
  7      2         1,000     2013-06-09
  |      |         |         |
  
  
  Loans
  loanid
  ------
  1
  2
  3
  4
  5
  |
  </code></pre>
  
  <p>As you can see, there's no transactions for loanid 4, just to make clear the point that there's no obligation for the transactions to exist for <strong>every</strong> loan.</p>
  
  <p>Now, what I'm trying to achieve is to sum up the amounts for the transactions of each loan. This first approach achieves this:</p>
  
  <pre><code>SELECT tr.tid,
         l.loanid,
         tr.entrydate,
         tr.amount,
         @prevLoan:=l.loanid prevloan,
         @amnt:=if(@prevLoan:=l.loanid, @amnt+tr.amount, tr.amount) totAmnt
  
  FROM (SELECT * FROM Transactions) tr
  
  JOIN (SELECT @prevLoan:=0, @amnt:=0) t
  
  JOIN Loans l
       ON l.loanid = tr.loanid
  
  
  GROUP BY l.loanid, tr.tid
  </code></pre>
  
  <p>Which achieves something like this:</p>
  
  <pre><code>tid    loanid    entrydate    amount    prevloan    totAmnt
  -----------------------------------------------------------
  1      1         2013-06-01   1,500     1           1,500
  3      1         2013-06-02   1,000     1           2,500
  6      1         2013-06-07   1,100     1           3,600  &lt;-- final result for loanid=1
  2      2         2013-06-01   1,500     2           1,500
  7      2         2013-06-09   1,000     2           2,500  &lt;-- final result for loanid=2
  4      3         2013-06-04   2,300     3           2,300  &lt;-- final result for loanid=3
  5      5         2013-06-04   2,000     5           2,000  &lt;-- final result for loanid=5
  |      |         |            |         |           |
  </code></pre>
  
  <p>As you can see, for each loan, the amount of the transactions belonging to it are getting sumed up in the totAmnt column, so the last transaction of each loan has the total sum of the transactions for the same loan.</p>
  
  <p>Now.... what I actually need is something to get the sum to be done in reversed order. I mean, for the same transactions of each loan, the sum still gets the same result, but I need the sum to be done from the last transaction of each loan up to the first one.</p>
  
  <p>I've tried the following, but to no avail (it's the same query as the last one, but with an Order By DESC on the FROM transactions table):</p>
  
  <pre><code>SELECT tr.tid,
         l.loanid,
         tr.entrydate,
         tr.amount,
         @prevLoan:=l.loanid prevloan,
         @amnt:=if(@prevLoan:=l.loanid, @amnt+tr.amount, tr.amount) totAmnt
  
  FROM (SELECT * FROM Transactions ORDER BY tr.entrydate DESC) tr
  
  JOIN (SELECT @prevLoan:=0, @amnt:=0) t
  
  JOIN Loans l
       ON l.loanid = tr.loanid
  
  
  GROUP BY l.loanid, tr.tid
  </code></pre>
  
  <p>I'm using tr.entrydate because is a more familiar way to say the order criteria, and besides that's what policy says is the valid order criteria, tid may say something but entrydate is the ordering column of the Transactions table...</p>
  
  <p>Using the previous query, I just get the same results I get with the first query, so I guess something must be missing there. What I need is to get results as the following:</p>
  
  <pre><code>tid    loanid    entrydate    amount    prevloan    totAmnt
  -----------------------------------------------------------
  6      1         2013-06-07   1,100     1           1,100
  3      1         2013-06-02   1,000     1           2,100
  1      1         2013-06-01   1,500     1           3,600  &lt;-- final result for loanid=1
  7      2         2013-06-09   1,000     2           1,000
  2      2         2013-06-01   1,500     2           2,500  &lt;-- final result for loanid=2
  4      3         2013-06-04   2,300     3           2,300  &lt;-- final result for loanid=3
  5      5         2013-06-04   2,000     5           2,000  &lt;-- final result for loanid=5
  |      |         |            |         |           |
  </code></pre>
  
  <p>As you can see the sum for each loanid gets the same final result, but the sum is done for the transactions in reversed order...</p>
  
  <p>Hope all this mess is clear... How can I achieve such a result?</p>