joining table with one needing a sum

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

joining table with one needing a sum

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-11-06 : 14:54:50

I must be looking at this way too long, it seems so trivial...but HELP...

table A contains a list of organizations with fy8 and fy9 expense projections..ie. one record, per org, per fy.

Table B contains a list of actual expenses for an organization by service by fy.

I want to sum the b.expense grouped by fy, by organization. and join it to the table of projected expense.

table A:

org fy projected
arg 8 50000
arg 9 55000
cms 8 45000
ddt 9 40000

table B:
org fy service exp
arg 8 pc 10000
arg 8 network 15000
arg 9 pc 12000
arg 9 network 10000

The result should be
table Joined
org fy projected actualexpense
arg 8 50000 25000
arg 9 55000 22000
cms 8 45000 null
ddt 9 40000 null

thanks for the help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 14:58:12

SELECT COALESCE(a.org, b.org) AS org, COALESCE(a.fy, b.fy) AS fy,
a.projected, b.exp AS actualexpense
from tablea AS a full join tableb AS b On b.org = a.org and b.fy = a.fy
order by 1, 2

E 12°55'05.63"
N 56°04'39.26"

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-11-06 : 16:36:07

close....now I need to sum the projected cost, and distinct the actual cost, per fy......I think...

the result of the coalesce is:

org fy projected averageITCost
age 08 132.76 100000 (for service #1)
age 08 200.58 100000 (for service #2)
age 08 400.25 100000 (for service #3)
age 08 300.50 100000 (for service #4)
age 08 145.43 100000 (for service #5)

...for the 21 service offerings....

result I am wanting is a single line per FY:

org fy projected averageITCost
age 08 87,523.43 100,000.00

where projected is a sum of the 21 service offering for that fy, and averageITCost is ..well..the averageIT cost indicated...

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 17:48:15

[code]
SELECT distinct COALESCE(a.org, b.org) AS org, COALESCE(a.fy, b.fy) AS fy,
a.projected, (select sum(exp) from @b where fy = b.fy and org = b.org) AS actualexpense
from @a AS a full join @b AS b On b.org = a.org and b.fy = a.fy
order by 1, 2[/code]

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-11-06 : 18:13:02

weeeeeeeeeeeee...thank you!

I had to reverse the expense and forecast numbers in your recommendation, but that NAILED it!!..

SELECT distinct COALESCE(a.agencycode,b.agencycode) AS agencycode, COALESCE(a.fy, b.fy) AS fy,
(select sum(projected) from servicecosts where fy=b.fy and agencycode=b.agencycode)as projectedexpense,b.avgit
from servicecosts AS a full join spending AS b On b.agencycode = a.agencycode and b.fy = a.fy
order by 1,2

end result is a 4 column table (agencycode,fy,projectedexpense,actualexpense)
two record per agency (for each FY 8 and 9) giving their projectedexpense and actual expense per FY...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources