simple query? - 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

simple query?

Author

Topic

sturner333
Starting Member

22 Posts

Posted - 2014-01-28 : 16:21:18

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-28 : 16:28:57

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

sturner333
Starting Member

22 Posts

Posted - 2014-01-28 : 16:50:09

I tried this and I still get multiple records for each project. any other ideas

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-28 : 17:14:30

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

sturner333
Starting Member

22 Posts

Posted - 2014-01-28 : 17:28:44

yes, that was the problem. thanks so much for the help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-28 : 17:37:23

you're welcome

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources