#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    5
    Rep Power
    0

    PHP Query Multiplication help...


    Hi guys,
    I am currently building a website to deal with different product information and sales with php. I am using SQL to sort the database and pull out information.

    The final thing i need to do is work out the total revenue of each product however the problem i am having is that the 'Price' column and 'SalesVolume' column are in two different tables and they need to be multiplied together.

    The two tables and column headings are as follows:

    Product
    ID
    Name
    Price

    MonthlySales
    ID
    ProductCode
    Month
    Year
    SalesVolume

    (ID and ProductCode are linked together in a relationship)

    I cannot see anything wrong with the syntax in my query however I can't see the problem.

    Here is the query I am using:

    Code:
    $describeQuery = "SELECT (Products.Price * MonthlySales.SalesVolume) as revenue From Products
    INNER JOIN MonthlySales
    ON
    (Products.ID = MonthlySales.ProductCode)
    GROUP BY Products.ID";
    
    
    
    $results = sqlsrv_query($conn, $describeQuery);
    
    echo '<table border="1" BORDERCOLOR=Black>';
    echo '<tr><th bgcolor = "LightBlue">Name</th><th bgcolor = "LightBlue" >ID</th> <th bgcolor = "LightBlue" >Sales</th></th> <th bgcolor = "LightBlue" >Year</th> </tr>';
    
    
    while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) 
      {   
      echo '<tr>';
    	echo '<td >' .$row['revenue'].'</td>'; 
    	echo '</tr>';
    	} 
    
    echo '</table>';
    
    
    sqlsrv_close($conn);
    As you can see all I'm trying to do is print a list of revenues in a table. But the query fails.

    Can anybody tell me what I'm doing wrong?
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,992
    Rep Power
    9397
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by requinix
    Aren't you missing a SUM?
    Of course!

    Thank you.

    However now I have encountered a different problem. I'm also trying to pull the name column from my product table. However I'm not sure where to put it in the query.

    I tried the below code, but it keeps failing.

    Code:
    $describeQuery = "SELECT Name, SUM(Products.Price * MonthlySales.SalesVolume) as revenue From Products
    INNER JOIN MonthlySales
    ON
    (Products.ID = MonthlySales.ProductCode)
    GROUP BY Products.ID";
    Any ideas?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    5
    Rep Power
    0
    UPDATE

    So now I have the year filter figured out but accessing the other columns is proving more difficult.

    Code:
    $describeQuery = "SELECT Products.ID, Products.Name, Products.Price * SUM(MonthlySales.SalesVolume) AS revenue FROM Products p
    
    INNER JOIN MonthlySales 
    ON
    (Products.ID = MonthlySales.ProductCode ) AND MonthlySales.Year = '$desiredYear' 
    GROUP BY Products.ID ";
  8. #5
  9. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,992
    Rep Power
    9397
    Moved to MS SQL, as my MySQL eyes don't see anything wrong.

    How about putting the year condition in a normal WHERE?

    [edit] Right, I missed the "year filter figured out" part...
    Last edited by requinix; March 30th, 2012 at 11:04 AM.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by requinix
    Moved to MS SQL, as my MySQL eyes don't see anything wrong.

    How about putting the year condition in a normal WHERE?
    The year filter is working fine, it's just trying to access the names from the product table that isn't working. I know it's something simple I just can't figure it out.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,127
    Rep Power
    4274
    Originally Posted by ncncnc
    I know it's something simple I just can't figure it out.
    the GROUP BY clause must include all non-aggregate columns mentioned in the SELECT clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by r937
    the GROUP BY clause must include all non-aggregate columns mentioned in the SELECT clause

    Thanks a lot!

    Problem Solved.

IMN logo majestic logo threadwatch logo seochat tools logo