SQL Server Forums
Profile | Register | 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
 simple query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sturner333
Starting Member

22 Posts

Posted - 01/28/2014 :  16:21:18  Show Profile  Reply with Quote
Sorry if this is redundant. I tried to find an appropriate thread.

I have 2 tables. one is a projects table. one is a bill of material(BOM) table. for any given project there will be several records in the BOM table. I would like to add the total dollars for each project but only show one line for each project. I know how to sum but I keep getting an output line for each project times the number of matching lines in the BOM.
Any help would be great!!

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/28/2014 :  16:28:57  Show Profile  Reply with Quote
something like this should work:

select <desired column list from projects>
       ,sum(b.cost) as [totalProjectDollars]
from   projects p
inner join BOM b on b.projectid = p.projectid
group by <desired column list from projects>


Be One with the Optimizer
TG
Go to Top of Page

sturner333
Starting Member

22 Posts

Posted - 01/28/2014 :  16:50:09  Show Profile  Reply with Quote
I tried this and I still get multiple records for each project. any other ideas
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/28/2014 :  17:14:30  Show Profile  Reply with Quote
you'll get one row for everything in your Group By. Are you sure you didn't include a column from your BOM table in the group by?
Post your code.

Be One with the Optimizer
TG
Go to Top of Page

sturner333
Starting Member

22 Posts

Posted - 01/28/2014 :  17:28:44  Show Profile  Reply with Quote
yes, that was the problem. thanks so much for the help!
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/28/2014 :  17:37:23  Show Profile  Reply with Quote
you're welcome

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000