CODEWITHSUNDEEP
phpsqlgroup-bysumgrouping
user1879415
user1879415

Reputation: 21

Query to group table data by one column and sum the product of two other columns

The following code displays the ordered products individual weight:

$weightsql = 'select op.products_name,
                     op.products_quantity,
                     p.products_weight 
              from ' . TABLE_ORDERS_PRODUCTS . ' op
              left join ' . TABLE_PRODUCTS . ' p on op.products_id = p.products_id
              where op.products_id =  ' . $pro['products_id'];
        
$weightq = tep_db_query( $weightsql );
        
while ($weight = tep_db_fetch_array( $weightq )) {
    if ($category_parent_id != 0) 
        $list_items[] = $weight['products_weight'] . '   ';
}

This shows the wieght for each of the products ordered. What I am stuck on is instead of showing seperate weights, I need to show a total weight. For example, if the product was ordered three times, and it weighs 7kg, the code at the moment is showing:

Product  7.00  7.00  7.00

How would I make it show the total weight, 21kg?

Upvotes: 0

Views: 154

Answers (4)

user1879415
user1879415

Reputation: 21

$weightsql = 'SELECT op.products_name, SUM(op.products_quantity * p.products_weight) AS weightsum
              FROM ' . TABLE_ORDERS_PRODUCTS . ' op
              LEFT JOIN ' . TABLE_PRODUCTS . ' p on op.products_id = p.products_id
              WHERE op.products_id =  ' . $pro['products_id'];

echo $weight['weightsum'];

Upvotes: 0

jeroen
jeroen

Reputation: 91734

If you have selected the product by its id, the easiest solution is to multiply the number of rows by the weight. No need to put it in an array.

Upvotes: 0

SISYN
SISYN

Reputation: 2259

Just set a counter to 0 before the loop and add do it during the loop, like this:

 $total = 0;
 $weightsql = 'select op.products_name, op.products_quantity , p.products_weight from ' . TABLE_ORDERS_PRODUCTS . ' op left join ' . TABLE_PRODUCTS . ' p on op.products_id = p.products_id where op.products_id =  '.$pro['products_id'];

 $weightq = tep_db_query( $weightsql );

 while ($weight = tep_db_fetch_array( $weightq )){
      if($category_parent_id != 0) {
           $list_items[] = $weight['products_weight'].'   ';
           $total += $weight['products_weight'];
      }

 }

The variable $total should then return 21.

Upvotes: 0

John Conde
John Conde

Reputation: 219804

Use array_sum()

array_sum() examples
<?php
$a = array(2, 4, 6, 8);
echo "sum(a) = " . array_sum($a) . "\n";

$b = array("a" => 1.2, "b" => 2.3, "c" => 3.4);
echo "sum(b) = " . array_sum($b) . "\n";
?>
The above example will output:
sum(a) = 20
sum(b) = 6.9

Upvotes: 3

Related Questions