CODEWITHSUNDEEP
phpmysqlgroup-bysum
Nanny Boy
Nanny Boy

Reputation: 107

php mysql calculate, sum and group by given parameters

I have an in-cart items and need a calculation to SUM shipment weight after multiplication by each item's qty.

Let says: 

items currently in-cart (pid): 11, 12, 67
items qty: 2, 5, 1

table for these pid:

enter image description here

PHP function to get MAX shipment cost and SUM weight group by merchant_code by given pids:

function get_g_checkout_shipment_fees($pids=array())
{

    $q  = "SELECT 
              merchant_code, 
              MAX(shipping_alt) AS max_g_shipment_cost, 
              SUM(weight) AS sum_g_shipment_weight ";
    $q .= "FROM products ";
    $q .= "WHERE pid IN(".implode(", ", $pids).") ";
    $q .= "GROUP BY merchant_code";

    $sql = $mysqli->query($q);

    if($sql->num_rows > 0){
        return $sql;
    }
    return false;
}

the output from above function:

enter image description here

Here is my question, I want to pass an additional array parameter (qty's) to function like:

function get_g_checkout_shipment_fees($pids=array(), $qtys=array())
{
    ...
}

I need to multiply each weight * qty and get the SUM shipment weight and GROUP BY merchant_code

Before find the SUM shipment weight, I need to do the necessary multiplication as illustrate:

enter image description here

and this is the results I want:

enter image description here

How can do this in the function? it's that possible?

any help is appreciated!

Upvotes: 0

Views: 317

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

The natural way to do this would be to store the shopping basket items in a table with their quantity. For instance, the following does this using a derived table:

SELECT merchant_code, 
       MAX(shipping_alt) AS max_g_shipment_cost, 
       SUM(weight) AS sum_g_shipment_weight,
       SUM(weight * qty) as sum_weight_qty
FROM (SELECT 11 as pid, 2 as qty UNION ALL
      SELECT 12, 5 UNION ALL
      SELECT 67, 1
     ) pp JOIN
     products p
     ON p.pid = pp.pid
GROUP BY merchant_code;

The same logic would hold if the product/quantity pairs were stored in another table or generated by a query of some sort.

Upvotes: 1

Related Questions