SQL Select Query Help - 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

SQL Select Query Help

Author

Topic

ncncnc
Starting Member

9 Posts

Posted - 2012-03-30 : 05:04:20

Hi all,

I'm currently building a website that uses data stored in SQL server. The website uses 2 seperate tables to gather data and displays the results of various queries.


$describeQuery = "SELECT ID, Name, (SELECT SUM(SalesVolume) as SalesVolume FROM MonthlySales WHERE ProductCode=Products.ID AND Year = '$desiredYear' AND Month = '$desiredMonth') AS num_sales FROM Products";

In this query I pull ID and name from my product table and the number of sales from my sales table.

However I also want to pull the month from my Sale stable, but I'm not sure of the Syntax.
I've tried

 $describeQuery = "SELECT ID, Name, (SELECT SUM(SalesVolume) as SalesVolume, Month FROM MonthlySales WHERE ProductCode=Products.ID AND Year = '$desiredYear' AND Month = '$desiredMonth') AS num_sales FROM Products";

I have a feeling it's something simple.
Can somebody help?

rajarajan
Starting Member

48 Posts

Posted - 2012-03-30 : 05:13:15

Use joins

Try This

Select p.id, p.name ,s.id,s.name ... from product p inner join with sales s on p.id = s.id
where p.name like '%Product1'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-30 : 05:17:06

You can just use your variable to get the month in the result set.
But outside the subselect.

$describeQuery = "SELECT ID, Name, '$desiredMonth' as Month (SELECT SUM(SalesVolume) as SalesVolume FROM MonthlySales WHERE ProductCode=Products.ID AND Year = '$desiredYear' AND Month = '$desiredMonth') AS num_sales FROM Products";

No, you're never too old to Yak'n'Roll if you're too young to die.

ncncnc
Starting Member

9 Posts

Posted - 2012-03-30 : 05:25:36

quote:
Originally posted by webfred

You can just use your variable to get the month in the result set.
But outside the subselect.

$describeQuery = "SELECT ID, Name, '$desiredMonth' as Month (SELECT SUM(SalesVolume) as SalesVolume FROM MonthlySales WHERE ProductCode=Products.ID AND Year = '$desiredYear' AND Month = '$desiredMonth') AS num_sales FROM Products";

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks for the reply.

I think this is on the right lines but it's not working. My tables/columns are:

MonthlySales
ID
ProductCode
Month
Year
SalesVolume

Products
ID
Name
Price

I think the $desiredMonth' as Month part is in the wrong place in that query.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-30 : 05:38:54

I don't know what's the meaning of "it's not working" in this case...

Here is another approach


select p.ID, p.NAME, dt.[Month], dt.SalesVolume
from Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Month] from monthlySales 
 where [Year] = '$desiredYear' AND [Month] = '$desiredMonth' 
 group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID

No, you're never too old to Yak'n'Roll if you're too young to die.

ncncnc
Starting Member

9 Posts

Posted - 2012-03-30 : 06:29:24

Thanks a lot it's working now.

webfred
Master Smack Fu Yak Hacker

8781 Posts

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

welcome