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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to write join statement for this		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anchoredwisdom
Starting Member

22 Posts
Posted - 11/09/2012 :  11:58:51  Show Profile  Reply with Quote
Hi Gurus,

I have a table with four columns.
ProductID, Year, Product , amount.

only 4 years , 4 products , 4 Ids , 4 amounts
say years 2009,2010,2011,2012,
Products A,B,C,D
ProductID,1,2,3,4 AND respective amounts,
now i just want sum (amounts ) group by these three attributes
But for 2010 Product C did not do any business hence there is no record for year 2010 against C id 3

I have display null against that missing year,id,product



( I tried to put my scenario in a generic way.)


Best Regards
S

TG
Flowing Fount of Yak Knowledge

USA
6065 Posts
Posted - 11/09/2012 :  15:05:29  Show Profile  Reply with Quote
>>( I tried to put my scenario in a generic way.)
Then here's a generic answer:

You need a table of all your groupings (product,year). say ProdYear_tbl
Then LEFT OUTER JOIN to your data table correlating on the grouping columns. SELECT the grouping values from the PRodYear table and sum the amount from your data table. That way you have a row for all possibilities even if the amount is null.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000