[SOLVED] Ineed help on MySQL/PHP calculation

[gnawz]

Hi guys.

 

I need to achieve a certain result through PHP and MySQL maths functions.

 

I have a tableasfollows

 

ItemID, ProductName, Quantity, Value

 

I can get the total quantity.

 

I need to get the total value. This requires me to get the total sum of the product of quantity and Value

 

something like SUM(Quantity*Value)

 

if I have 3 records as follows:

 

ItemID ProductName Quantity Value

1                Fruits        3        100

2                Books        5        200

3                Machines    2        500

 

The total value of fruits is 300, books 1000 and machines 1000. Total value of all is 2300.

 

How do I achieve this (2300) in SQL and PHP?

 

ie 3 * 100

  5 * 200

  2 * 500

 

 

I hope I'm clear

 

[Bricktop]

Hi gnawz,

 

I wonder if something like this may work:

 

$valuequery = "SELECT ProductName, SUM(value) FROM TABLE_NAME GROUP BY ProductName";
$quantityquery = "SELECT ProductName, SUM(quantity) FROM TABLE_NAME GROUP BY ProductName";

if ( ($valueresult = mysql_query($valuequery)) && ($quantityresult = mysql_query($quantityquery)) ) { 

     while (($row=mysql_fetch_assoc($valueresult))||($row=mysql_fetch_assoc($quantityresult))) { 

        $result = $row['SUM(value)'] * $row['SUM(quantity)'];
echo $result;

      } 
   } 

 

I'm not sure if the above would work, in theory it should get the total quantity of each Product, then get the quantity of each price for that product, multiple it and return the result.

 

I may be way off with this so apologies in advance if I am but give it a go and see what results you get.

[jcombs_31]

SELECT SUM(quantity * value) AS total from table;

 

 

[Bricktop]

Hi jcombs_31, that query would effectively do 10 x 8 wouldn't it? 

 

I might be wrong as that statement is what I was going to suggest at first but thought it would give the wrong number gnawz is looking for.

[jcombs_31]

Hi jcombs_31, that query would effectively do 10 x 8 wouldn't it? 

 

I might be wrong as that statement is what I was going to suggest at first but thought it would give the wrong number gnawz is looking for.

 

No, that query will give exactly the result the OP is looking for.  Don't know where you came up with 10*8. There is no need for multiple queries or php to do the math.

[gnawz]

Thanks guys.

However,what I need is to get the total or sum of quantity * value not sum of quantity * sum of value.  ie (quantity * value + quantity * value + quantity * value),etc like in my example table above.

[PFMaBiSmAd]

The query that jcombs_31 posted gives 2300 as the total. That is what you stated the result should be. Did you even try it?

[gnawz]

Hi? It looks like it should be working but I get "Resource id#8" when I "echo $result;"

[PFMaBiSmAd]

That's because a query returns a result resource and you need to use one of the mysql_fetch_xxxx statements to get a row from the result set - http://us3.php.net/manual/en/function.mysql-fetch-assoc.php

[gnawz]

Im using mysql_fetch_array() and my query is;

 

$sql = "SELECT SUM(Quantity *Value) FROM stocktable AS TOTAL";

$result = dbQuery($sql);

 

while($row = mysql_fetch_array($result))

{

$totalvalue = $row["SUM(Quantity*Value)"];

}

 

Also,I have noticed one may omit the phrase "as total" from the sql.

 

Besides that,what could be the issue?

[mikesta707]

he made it as total so you could refer to it as

$row['total'];

 

so change it to

$totalvalue = $row['total'];

[gnawz]

Actually it worked! Just sth missing in SQL. I will try row as total as well. Good for me to learn,thanks