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:
(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:
"SELECT SUM(Products.Price * SUM(MonthlySales.SalesVolume)) as revenue FROM Products
INNER JOIN MonthlySales ON(Products.ProductCode = MonthlySales.id) GROUP BY Products.ProductCode";
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.
"SELECT SUM(Products.Price * MonthlySales.SalesVolume)as revenue, Name FROM Products
(Products.ID = MonthlySales.ProductCode)
SELECT Year From MonthlySales WHERE Year = 1991;
GROUP BY Products.ID";
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?
Originally Posted by anonymousmofo
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
, Products.Price * SUM(MonthlySales.SalesVolume) AS revenue
ON MonthlySales.id = Products.ProductCode
AND MonthlySales.Year = 1991
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".