How do i get a running total from the previous transaction?

Hi All

I'm trying to get a running total sum in my view from my transactions table which holds transactions for different customers using a foreign key. In the view the transactions are then filtered down by the user selecting the relevant customer.

For reference, i have a tx_amount_in field which records the values added and a tx_amount_out field which records the values deducted.

In my view, i have a @foreach looping over each iteration of the transactions and after each transaction i would like to show the value remaining whether its added or deducted but for the life of me i cant figure it out.

So far i've got it giving me the sum of the last transaction OR another way i tried wasn't calculating correctly at all.


@forelse($transactions as $tx)
    <tr>
        @php
            $runningTotal = 0; 
            $runningTotal += $tx->tx_amount_in;
            $runningTotal -= $tx->tx_amount_out;
        @endphp
        
        <td class="table-c-row card-title">
            @if ($runningTotal >= 0)
                <p>£{{ $runningTotal }} remaining</p>
            @else
                <p>£{{ abs($runningTotal) }} excess</p>
            @endif
        </td>
        <td class="table-c-row">
            @if($tx->tx_in != 0)
            <div class="card-title-alt text-right text-green"> <i class="fad fa-coin mr-1"></i> {{ number_format($tx->tx_amount_in, 2) }}</div>
            @else
            <div class="card-title-alt text-right text-red"> <i class="fad fa-coin mr-1"></i>- {{ number_format($tx->tx_amount_out, 2) }}</div>
            @endif
        </td>
    </tr>
@endforeach

Please can someone advise how best to tackle this. Any questions, please let me know.

Thanks in advance.

dcx

2 years ago

Level 1

Maybe you can share the foreach?

jlrdw

2 years ago

Level 75

Do it in the query, like:

        $checksearch = $checksearch . "%";
        $pagingQuery = " LIMIT $offset, $rowsperpage";

        $sql = "SELECT OD.checkid, OD.transdate, OD.transdescribe, OD.widthdraw, OD.deposit, OD.isclr,";
        $sql = $sql . " (SELECT (Sum(IFNULL(deposit, 0)) - Sum(IFNULL(widthdraw, 0))) FROM checks";
        $sql = $sql . " WHERE checkid<=OD.checkid) AS RunningSum";
        $sql = $sql . " FROM checks AS OD" . $pagingQuery;
        $sth = $this->db->pdoPrepare($sql);
        //$sth = $conn->prepare($sql);
        $sth->execute();
        $results = $sth->fetchAll(\PDO::FETCH_ASSOC);
        return $results;

Looks like:

Alt image

Not your data, just adapt to eloquent. Notice the "running total" far right column.

Just suggestion. Also there are many other tutorials online such as https://www.tutorialspoint.com/how-to-create-a-cumulative-sum-column-in-mysql

1 like

ollie_123

2 years ago

Level 6

Hi @jlrdw, thanks for this. As the project is already well underway i need to run this dynamically in the view as to start calculating the totals in SQL going forward from now would be incorrect as there is already a transaction history.

jlrdw

2 years ago

Level 75

@ollie_123 A running total has nothing to do with data already stored, it's just a query with a derived column.

The total column is not stored in the database. You've never written a report with totals? If so it is the same.

Please or to participate in this conversation.