CODEWITHSUNDEEP
mysql
Rich
Rich

Reputation: 1

Updating table using group by sum

I have two tables. One is a detail table that contains quantity info and has the following structure

> mysql> DESCRIBE summary_finished;
+---------------+---------------+------+-----+---------+
| Field         | Type          | Null | Key | Default |
+---------------+---------------+------+-----+---------+
| db_id         | int(11)       | NO   | PRI | NULL    |
| finished_id   | text          | NO   |     | NULL    |
| finished_name | text          | NO   |     | NULL    |
| qoh           | int(11)       | NO   |     | NULL    |
| value         | decimal(10,2) | NO   |     | NULL    |                
+---------------+---------------+------+-----+---------+


    mysql> DESCRIBE detail_finished;
    +-------------+---------------+------+-----+---------+
    | Field       | Type          | Null | Key | Default |
    +-------------+---------------+------+-----+---------+
    | db_id       | int(11)       | NO   | PRI | NULL    |
    | finished_id | text          | NO   |     | NULL    |
    | quantity    | int(11)       | NO   |     | NULL    |
    | value       | decimal(10,2) | NO   |     | NULL    |
    +-------------+---------------+------+-----+---------+

In detail_finished there are many items that have the same finished_id. What I am trying to accomplish is combining items that have the same finished_id and update that sum to the summary_finished.qoh field. This is what I have so far:

function set_qty($total, $finished_id){
    global $connection;
    $query="UPDATE summary_finished 
    SET qoh={$total} 
    WHERE finished_id='$finished_id'";
    $quantity=mysql_query($query, $connection);
    confirm_query($quantity);
    }


$query="SELECT finished_id, SUM(quantity) FROM detail_finished GROUP BY finished_id";
$result=mysql_query($query, $connection) or die(mysql_error());

while($row = mysql_fetch_array($result)){
  set_qty($row['SUM(quantity)'], $row['finished_id']);
   }

I know the sums are calculating correctly as I can echo them straight out, however I cannot get my table to update. Forgive me for the sloppy code I am very new with mysql entirely. Thank you in advance for the help.

Upvotes: 0

Views: 382

Answers (1)

Nicola Cossu
Nicola Cossu

Reputation: 56357

I don't know why you use text type to store id. By the way

 update summary_finished as sf
 inner join (
 select finished_id, 
        sum(quantity) as total
 from detail_finished 
 group by finished_id )as t
 set sf.qoh = t.total
 where sf.finished_id = t.finished_id

Upvotes: 1

Related Questions