Query Syntax Problem. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Syntax Problem.

Author

Topic

ncncnc
Starting Member

9 Posts

Posted - 2012-03-30 : 10:10:57

Hi all,

I'm working on a website that uses sales data to make tables.

In my current table I'm trying to work out revenue. I have been successful in doing this however I am having difficulties pulling any other colums from the table.
This is my working revenue code. This pulls Sales from on table and Price from another and Multiplie sthem together.

$describeQuery = "SELECT SUM(Products.Price  * MonthlySales.SalesVolume ) as revenue  From Products 
INNER JOIN MonthlySales 
ON
(Products.ID = MonthlySales.ProductCode )
GROUP BY Products.ID ";

Now I want to be able to Pull the name and filter the data by year but I don't know the correct syntax.

$describeQuery = "SELECT Name, SUM(Products.Price  * MonthlySales.SalesVolume ) as revenue  From Products WHERE year = 1990
INNER JOIN MonthlySales 
ON
(Products.ID = MonthlySales.ProductCode )
GROUP BY Products.ID ";

This is what I thought would be correct but the query keeps failing.

Any ideas?

Thank you

ncncnc
Starting Member

9 Posts

Posted - 2012-03-30 : 10:32:54

UPDATE

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

$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 ";

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-30 : 17:27:27

what are other columns you're trying to access?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pduffin
Yak Posting Veteran

68 Posts

Posted - 2012-03-30 : 17:34:43

Either of these should work.

select products.id, products.name, sum(products.price * monthlysales.salesvolume) as revenue
from products p
inner join monthlysales on products.id = monthlysales.productcode and monthlysales.year = '$desiredyear'
group by products.id,products.name

or

select products.id, products.name,a.revenue
from products p
join
(
select monthlysales.productcode, sum(products.price * monthlysales.salesvolume) as revenue
from monthlysales
where monthlysales.year = '$desiredyear'
group by monthlysales.productcode
) a on products.id = a.productcode

Likes to run, hates the runs!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources