SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $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
    ***********

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •