如何在新col中插入由另一个col分组的col的总和

Question

I'm trying to multiply 2 columns "qty*price_dl" of table and but the value in new column "total1" in the same table and this work with me without problems

after that trying to get sum of "total1" column grouped by "seq" column and but the values in "total2" column in the same table I'm stuck in process of summation it doesn't work with me this is the code:

<?php
require_once("dbconfig.php");
$q=mysql_query('select * from sale_menu')
or die(mysql_error());
echo "<table border='2'>
<tr>
    <th>Select</th>
    <th>qty</th>
    <th>price_dl</th>
    <th>total1</th>

</tr><tr>";
while($rw=mysql_fetch_array($q)){
    ?>
    <td><input type="checkbox" value="<?php echo $rw['seq']; ?>" name="seq[]" ></td>
    <td><b><?php echo $rw['qty']; ?></b></td>
    <td><b><?php echo $rw['price_dl']; ?></b></td>
    <td><b><?php echo $rw['total1']; ?></b></td>
    <tr>
    </tbody>
    <?php } ?>
    <?php
    error_reporting(0);
    $seq=$_POST['seq'];
    if (isset($_POST['submit']))
    {   
        $impid=implode(", ",$seq);
        $qdel=mysql_query("SELECT * FROM sale_menu WHERE seq IN(".$impid.")");
        while($row=mysql_fetch_array($qdel)){
            $qty=$row['qty'];
            $price_dl=$row['price_dl'];
            $qdel=mysql_query("UPDATE sale_menu set total1=qty*price_dl WHERE seq IN(".$impid.")");
            $qdel=mysql_query("UPDATE sale_menu set total2=sum(total1) WHERE seq IN(".$impid.")");
        }
    }
    ?>
    <button name="submit" type="submit" value="Checked" id="submit" >Move</button>

this is where I stuck:

$qdel=mysql_query("SELECT * FROM sale_menu WHERE seq IN(".$impid.")");
$qdel=mysql_query("select sum(total1) as total from sale_menu group by seq");
while($row=mysql_fetch_array($qdel)){
$total =  $row['total'];
$qdel=mysql_query("UPDATE sale_menu set total1=qty*price_dl WHERE seq IN(".$impid.")");
$qdel=mysql_query("UPDATE sale_menu set total2= $total WHERE seq IN(".$impid.")");

Answer 1

You cant put total1 as column here because you need to make query inside query and there is two ways to do it

The first it to do nested query like this

$qdel=mysql_query("UPDATE sale_menu set total2= (select sum(total1) as total from table_name) WHERE seq IN(".$impid.")");

The second is way much easier just make another query before this query and store the value of sum(total1) inside a variable then you can use the variable in this query for example

$q=mysql_query('select sum(total1) as total from table_name')
while($rw=mysql_fetch_array($q)){

   $total =  $rw['total'];
 }
$qdel=mysql_query("UPDATE sale_menu set total2= $total WHERE seq IN(".$impid.")");

I hope it works for you