Multiplying Different Tables

[anonymousmofo]

I am currently building a website which deals with sales of cars. I need to be able to work out the total revenue of the cars by calculating the total sales * price. However the problem i am having is the Price and total sales are in 2 different tables.

 

The First Table:

 

Cars Table:

Car Code, Make, Model, Price

 

The second Table:

 

Sales Table:

Car ID, Month, Year, Total Sales

 

(Car Code and Car ID are linked together with a relationship)

 

How can i get the price of one car and multiply it with the total sales figure to get the revenue?

 

Any help would be much Appreciated.

 

 

 

[Muddy_Funster]

something like

SELECT 
(cars.price * sales.totalSales) as revenue 
FROM cars 
INNER JOIN sales 
ON
(
  cars.`car code` = sales.`car id`
)
GROUP BY cars.`car code`

[anonymousmofo]

I have tried your code which you suggested however it is coming up with an error message:

 

Array ( [0] => Array ( [0] => IMSSP [sqlSTATE] => IMSSP [1] => -14=> -14 [2] => An invalid parameter was passed to sqlsrv_fetch_array. [message] => An invalid parameter was passed to sqlsrv_fetch_array. ) )

 

I have checked all of the column names and they match up fine with the ones i am using.

 

any thoughts?

[Muddy_Funster]

why would you post an SQL Server question in the MySQL forum, and not stipulate that you are in fact not using MySQL?

What are your exact table names and column lables?

[anonymousmofo]

My exact table names are as follows:

 

Cars

 

CarCode,Model, Price

 

MonthlySales

 

ID, ProductCode, Month, Year, TotalSales

 

CarCode and ProductCode are the same thing but in two different tables.

[The Little Guy]

I may be wrong, but I think you need to sum up TotalSales and multiply that by the price.

 

SELECT 
(Cars.Price * SUM(MonthlySales.TotalSales)) as revenue 
FROM Cars 
JOIN MonthlySales
ON
(
  Cars.CarCode = MonthlySales.ProductCode
)
GROUP BY Cars.CarCode

[anonymousmofo]

Have just tried your SUM code and that you posted however still no joy. I am getting the same error as before, an invalid parameter was passed. Could this be because i am missing some single quotes off one of the column headings? I think it will be something simple

[Muddy_Funster]

let's see the exact query as you are trying to run it

[The Little Guy]

Check Example #1:

 

http://www.php.net/manual/en/function.sqlsrv-errors.php

 

Do something like that with your errors, and lets see what they are.

[anonymousmofo]

let's see the exact query as you are trying to run it

 

$describeQuery = "SELECT (Cars.Price * SUM(MonthlySales.TotalSales))  as revenue FROM Cars INNER JOIN MonthlySales ON(Cars.'CarCode' = MonthlySales.'ProductCode')GROUP BY Cars.'Car'";

$query = sqlsrv_query( $link, $describeQuery);	


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>';	

if(sqlsrv_errors() !== null) 
{
print_r(sqlsrv_errors());
}


sqlsrv_close($link);





?>
</font>
</body>
</html>

[Muddy_Funster]

yeah, you don't put quotes around table names or column lables (the example I used was backticks - they are something different - and were only there because there were spaces in the names you gave initialy) if you do that SQL Server thinks you are trying to pass it a string value instead of a refference

[anonymousmofo]

I have taken the qoutes out of the code and it still gives me the same error message

 

$describeQuery = "SELECT (Cars.Price * SUM(MonthlySales.TotalSales))  as revenue FROM Car INNER JOIN MonthlySales ON(Cars.CarCode = MonthlySales.ProductCode)GROUP BY Cars.CarCode";

$query = sqlsrv_query( $link, $describeQuery);	



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>';	

if(sqlsrv_errors() !== null) 
{
print_r(sqlsrv_errors());
}


sqlsrv_close($link);

?>
</font>
</body>
</html>

[mikosiko]

debugging 101:

$describeQuery = "SELECT (Cars.Price * SUM(MonthlySales.TotalSales))  as revenue
                    FROM Car INNER JOIN MonthlySales ON(Cars.CarCode = MonthlySales.ProductCode)GROUP BY Cars.CarCode"; 

// TEMPORARILY ECHO YOUR RAW QUERY AND TAKE A CLOSE LOOK TO SEE IF IT IS SYNTACTICALLY  CORRECT (.. missing spaces, bad sentences, missing values, etc..etc)
echo "My Query IS : " . $describeQuery . "<br />";

// IF PREVIOUS ECHO LOOKS OK... EXECUTE THE QUERY
$query = sqlsrv_query( $link, $describeQuery);	

// AND CONTROL IF THE QUERY WAS EXECUTED WITHOUT ERRORS, OTHERWISE DISPLAY THE ERROR
if (!$query) {
   die("QUERY ERROR: " . sqlsrv_errors());
}
.... REST OF YOUR CODE

[anonymousmofo]

I implemented your code into my website and when I run it it displays 'Query error: Array'.

Thoughts?

[fenway]

Solved how?