user3613026 user3613026 - 4 months ago 28
SQL Question

MySQL SELECT GROUP BY

How to select id,total from table_order and create list of id, sum the total according/group by add_by? And then insert the list of id and sum of total into table_bill according to add_by. This what i've so far.

//get unpaid orders
$sql = mysql_query("SELECT id,total,add_by FROM `order` WHERE status = '1'");
$res = mysql_num_rows($sql);

if($res>=1){
$tot = 0;
$ids = '';
while($row = mysql_fetch_array($sql)){
$ids .= $row['id'].",";
$tot += $row['total'];
$uid = $row['add_by'];
}
$ids = rtrim($ids, ",");

/*echo "<br>List of ID: ".$ids;
echo "<br>Total: ".$tot."<br>";
echo "<br>Add by: ".$uid."<br>";*/

//create bill
$sql_bill = "INSERT INTO `bill` (list,amount,user) VALUES('$ids','$tot','$uid')";
$query = mysql_query($sql_bill);
}


For example data from table_order: (id | total | add_by)

103 | 350 | 4
104 | 450 | 6
105 | 250 | 6
106 | 400 | 4


then insert into table_bill: (list | amount | user)

103,106 | 750 | 4
104,105 | 700 | 6

Answer

The query for obtain your result shuold be

select group_concat(id) ,sum(total) ,add_by
from `order`
group by add_by

and then you could do with a single query command

insert into table_bill (list , amount , user)
select group_concat(id) ,sum(total) ,add_by
from `order`
group by add_by