sum value from mysql in php

Question

Hi,

Anyone knows how to get sum of number?

For example i have mysql column name package.

Package
3
4
1
3
4

If package 3, the value should be usd 5, if package 4, value should be usd 10, if package 1, value should be usd 1 and so on.

So the total value above should be => 5 + 10 + 1 + 5 + 10 = 31

So how do i get sum of it in php?

i tried array_sum .. but it gives me error, im not very expert in php.. Please help me

Thanking in advance.

Answer 1

Get the database to do the work:

select sum(CASE WHEN package = 3 THEN 5 ELSE 0 END)
       + sum(CASE WHEN package = 4 THEN 10 ELSE 0 END)
       + sum(CASE WHEN package = 1 THEN 1 ELSE 0 END) AS total
from table_name

The right way of doing it, if you've got more than a few values be to modify the table to contain the amount field so you can do:

select sum(amount) from table_name

Alternatively have another table that you contained the relationship between the values (package, amount), and then do

select sum(package_amount) 
from table_name, lookup_values
where table_name.package = lookup_values.package

Or, if you really want to do it in PHP:

$amount = array(3=>5, 4=>10, 1=>1);  // define the amount of each value
$result = mysql_query("SELECT id FROM table_name");
$sum = 0;

while ($row = mysql_fetch_assoc($result))
{
    $sum += $amount[ $row['id'] ];
}

Answer 2

hmm lemme try, thanks

Answer 3

You can do it in purely SQL like Richard did, the PHP version would be something like:

$result = mysql_query("SELECT `number` FROM `packages`");

while ($row = mysql_fetch_assoc($result))
{
    $curr = $row['number'];
    if ($curr == 3) $sum += 3;
    if ($curr == 4) $sum += 10;
    if ($curr == 1) $sum++;
}

This is assuming your database table is named 'packages' and the column that has the different package numbers is called 'number'. At the end of the loop you will be left with the sum in the $sum variable. Not sure if this is necessarily the most efficient way to do this in PHP.

Answer 4

I think a neater way to do it in PHP would be to declare an associative array of weights, this separates the data from the algorithm:

$weight=array(
  1=>1,
  3=>3,
  4=>10
);

$result = mysql_query("SELECT `number` FROM `packages`");

$sum=0;
while ($row = mysql_fetch_assoc($result))
{
    $n = $row['number'];
    if (isset($weight[$n])) 
    {
         $sum+=$weight[$n];
    }
    else
    {
          #handle error somehow, maybe with a default weight
          die("Unexpected value $n");
     }
}

An better alternative would be to make this entirely data driven and put your weights in a table

#create and populate the weights table
create table weight (number int, weight int, primary key(number));
insert into weight (number,weight) values (1,1),(3,3),(4,10);

#figure out total weight
select sum(weight) from package inner join weight using(number);

With weights in the database, you could provide a UI to alloww users of your application to adjust the weights.