CODEWITHSUNDEEP
phpmysqlsum
user3236149
user3236149

Reputation: 167

SUM previous value in php

How can I create a function which can SUM the previous row with the new row and show me the result per each row?.

Like this in the column"SALDO":

| Cantidad           | nombre del producto | Precio compra | Precio DNM | Precio venta | Total compra | Saldo   | 
+--------------------+---------------------+---------------+------------+--------------+--------------+---------+
|                 14 | Sargenorig          |          6.00 |       9.00 |        10.00 |           84 |      84 | 
|                  9 | nombre producto     |        100.00 |     100.00 |       120.00 |          900 |     984 | 
| Total de productos |                     |               |            |              | Total        | Reporte | 
| 23                 |                     |               |            |              |              |     984 |

tExiste is the total of product quantity this is the sum of all existence minus the sum of all the sales of each product

Tott is the total of total purchases this is the sum of all existence minus the sum of all the sales of each product, the result is multiplied by the purchase price of each product

$sql = "SELECT nombreProd, compra, venta, dnm, 
SUM(existencia - vendido) AS tExiste, 
 (SUM(existencia - vendido) * compra) AS Tott
FROM PRODUCTOS WHERE f_producto BETWEEN 
'".$fromdate."' AND '".$todate."' GROUP BY code 
ORDER BY id DESC";
$result = $conn->query($sql);
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
    echo '
        <tbody>
            <tr>
                <td>'.$row["tExiste"].'</td>
                <td class="center">'.$row["nombreProd"].'</td>
                <td class="center">
                <span class="add-on">'.$moneda.' </span>'.$row["compra"].'
                </td>
                <td class="center">
                <span class="add-on">'.$moneda.' </span>'.$row["dnm"].'
                </td>
                <td class="center">
                <span class="add-on">'.$moneda.' </span>'.$row["venta"].'
                </td>
                <td>
                <span class="add-on">'.$moneda.' </span>'.$row["Tott"].'
                </td>    
                <td>
                <span class="add-on">'.$moneda.' </span>
                </td>  // Here is where I need show the sum of each previous row with the new one              
            </tr>                         
        </tbody>
    '; }

and for last I need to show the total amount of those SUM

EDIT

I need to sum the previous Tott to the next Tott (Tott + Tott), not the sum of existence with the total (tExiste + Tott)...

e.g.: R1 : 84 + R2 : 900 = 984 + R3 : 150 = 1134...etc

Thank you

Upvotes: 1

Views: 865

Answers (2)

Machavity
Machavity

Reputation: 31614

If I were you, I'd just do a simple sum in PHP of all the values you need summed and echo that out (I didn't follow which you needed summed so I gave you an example). I always recommend that, if possible, you should have PHP do the work and not your database.

$prev = 0;
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
echo '
    <tbody>
        <tr>
            <td>'.$row["tExiste"].'</td>
            <td class="center">'.$row["nombreProd"].'</td>
            <td class="center">
            <span class="add-on">'.$moneda.' </span>'.$row["compra"].'
            </td>
            <td class="center">
            <span class="add-on">'.$moneda.' </span>'.$row["dnm"].'
            </td>
            <td class="center">
            <span class="add-on">'.$moneda.' </span>'.$row["venta"].'
            </td>
            <td>
            <span class="add-on">'.$moneda.' </span>'.$row["Tott"].'
            </td>    
            <td>
            <span class="add-on">'.$moneda.' </span>
            </td>
            <td>' . ($row["Tott"] + $prev) . '</td>   
        </tr>                         
    </tbody>
';
$prev = $row["Tott"];
}

Upvotes: 2

J-Dizzle
J-Dizzle

Reputation: 3191

You can get the total through SQL with WITH ROLLUP.

From documentation

SELECT IFNULL(year,"Total") as year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;


+-------+-------------+
| year  | SUM(profit) |
+-------+-------------+
|  2000 |        4525 |
|  2001 |        3010 |
| Total |        7535 |
+-------+-------------+

Upvotes: 0

Related Questions