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

[mehidy]

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.

[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
***********

[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 --

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