# Calculating total quantity & group by field from 2 table in php.

• May 27, 2012, 09:04
mehidy
Calculating total quantity & group by field from 2 table in php.
Dear All

I have 2 table, one is tableA (field are tableAid, orderno, orderqty, shipmentmonth) & another is tableB (field are tableBid, tableB_tableAid, buyh, buyr).

TableA have the order no#, qty, shipment month & other details, TableB has buyh, buyr & others details.

I want to have the total quantiy for each buyh of each shipmenthmonth (data type 2012-01, 2012-02...etc) group by buyh.

How can i do this with php? Plz help.
• May 27, 2012, 10:14
mehidy
\$details = "SELECT buyh FROM tableB, SUM(orderqty) AS sum FROM tableA WHERE shipmonth BETWEEN '2012-01' AND '2012-12' AND tableA.tableAid=tableB.tableAid GROUP BY buyh";

\$details_result = mysql_query( \$details)
or die ( "Couldn't get Products: ".mysql_error() );
while ( \$det_row = mysql_fetch_array ( \$details_result ) )
{
echo "<b>&nbsp ".\$det_row[ 'buyh' ].":&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp </b>".\$det_row[ 'sum' ]."<br>";
}
?>

While running above query it shows belo msg.

***********
Couldn't get Products: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(orderqty) AS sum FROM tableA WHERE shipmonth BETWEEN '2012-01' AND '2012-' at line 1
***********
• May 27, 2012, 13:15
r937
the error message is telling you exactly where the error occurs, right on the word SUM

once you say FROM (initiating the FROM clause), you cannot add any more items to the SELECT clause

try it like this --
Code:

```SELECT tableB.buyh     , SUM(tableA.orderqty) AS sum   FROM tableA INNER   JOIN tableB     ON tableB.tableAid = tableA.tableAid  WHERE tableA.shipmonth BETWEEN '2012-01' AND '2012-12' GROUP     BY tableB.buyh```