Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2012
    Posts
    7

    Unanswered: Cross table Multiplication Query

    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 believe there is.

    Here is the query I am using:

    Code:
    "SELECT SUM(Products.Price * SUM(MonthlySales.SalesVolume))  as revenue FROM Products
    INNER JOIN MonthlySales ON(Products.ProductCode = MonthlySales.id) GROUP BY Products.ProductCode";
    Last edited by anonymousmofo; 03-30-12 at 09:49.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Products.ProductCode
         , Products.Price * SUM(MonthlySales.SalesVolume) AS revenue 
      FROM Products
    INNER 
      JOIN MonthlySales 
        ON MonthlySales.id = Products.ProductCode 
    GROUP 
        BY Products.ProductCode
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2012
    Posts
    7
    Ahh yes thank you much appreciated, However I also want to be able to pull other columns out of other tables in the same query but it doesnt seem to be allowing me to do so.

    I want the 'Year' out of the MonthlySales table and also the 'Name' out of the Products table to also be pulled out by this query but no matter where I put it in the query it will not work.

    Code:
    "SELECT SUM(Products.Price * MonthlySales.SalesVolume)as revenue, Name FROM Products 
    INNER JOIN 
    MonthlySales 
    ON 
    (Products.ID = MonthlySales.ProductCode) 
    SELECT Year From MonthlySales WHERE Year = 1991; 
    GROUP BY Products.ID";
    Any help would be greatly appreciated

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anonymousmofo View Post
    However I also want to be able to pull other columns out of other tables in the same query
    may i ask why you did not mention this earlier?


    Quote Originally Posted by anonymousmofo View Post
    I want the 'Year' out of the MonthlySales table and also the 'Name' out of the Products table to also be pulled out by this query
    Code:
    SELECT Products.ProductCode
         , Products.Name
         , Products.Price * SUM(MonthlySales.SalesVolume) AS revenue 
      FROM Products
    INNER 
      JOIN MonthlySales 
        ON MonthlySales.id = Products.ProductCode 
       AND MonthlySales.Year = 1991
    GROUP 
        BY Products.ProductCode
         , Products.Name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2012
    Posts
    7
    Yes i should of mentioned it, I was anticipating I was going to be able to do that part. I have implemented your code however it comes back with an error message saying "An invalid Parameter has passed".


    Any thoughts?

  6. #6
    Join Date
    Mar 2012
    Posts
    7
    The year filter is working fine with your code however it does not seem to like pulling anything out of the product table with the 'Product.Price', 'Product.Name' etc.

    Ideas?

    Thanks

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anonymousmofo View Post
    Ideas?
    not until you show your actual query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2012
    Posts
    7
    Here is my Query:


    Code:
    $describeQuery =	"SELECT SUM(Products.Price * MonthlySales.SalesVolume) AS revenue FROM Products
    						INNER JOIN MonthlySales 
    						ON MonthlySales.ProductCode = Products.ID AND MonthlySales.Year = 1991
    						GROUP BY Products.ID";		
    
    
    
    	$query = sqlsrv_query($link, $describeQuery);
    		
    	
    	echo "Year = $desiredYear";
    
    	echo '<table border = "2" bordercolor = "Black">';
    	echo '<tr><th bgcolor = "Black">Revenue</th></tr>';
    
    	while($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)) 
    	{
    
    	echo '<tr>'; 	
    	
    	echo '<td>' . $row['revenue'] . '</td>'; 				
    	echo '</tr>';
    
    	} 
    
    	echo '</table>';	
    	
    
    
    	sqlsrv_close($link);
    
    
    
    	
    
    ?>
    </font>
    </body>
    </html>
    This seems to pull in the revenue and year fine however when I add the following line and try to pull in the names it does not work.

    Code:
    "SELECT Products.ID, Products.Name, Products.Price * SUM(MonthlySales.SalesVolume)

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    go back to post #4

    notice where i put Products.Name

    hint: more than once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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