We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# SQL Join with Aggregates problem

on
Medium Priority
689 Views
I have four tables something like this:

PROJECTS
------------------
ID        NAME
1        Project 1
2        Project 2
3        Project 3

APPROPRIATIONS
---------------------------
APPR_ID     PROJ_ID    APPR_DATE    APPR_AMT
1                   3               12-1-06           12000
2                   2               12-1-07             7000
3                   2               12-1-08             5000
4                   1               12-1-05           50000
5                   3               12-1-07            8000
6                   3               12-1-08            6000

BUDGETS
---------------
BUD_ID     PROJ_ID       BUD_DATE      BUD_AMT
1                  1                12-1-09           5000
2                  3                12-1-09           4000

EXPENDITURES
-------------------
EXP_ID       PROJ_ID       EXP_DATE      EXP_AMT
1                  2                   1-1-08            3000
2                  2                   6-1-08            4000
3                  2                   1-1-09            5000
4                  1                   1-1-06           10000
5                  1                   3-1-06             2000

I need to summarize them to show:
1) Project ID and name
2) Budget date and amount
3) Total appropriations prior to budget date
4) Total expenditures prior to budget date

When I LEFT JOIN and SUM the tables and amounts, the fact that some projects have multiple related rows in one table but only one related row in another causes the SUMs to be off, since some values are duplicated.

How do I make this work correctly?
Comment
Watch Question

## View Solution Only

Commented:
Here you go -
``````SELECT PR.[ID], PR.[NAME], BU.BUD_DATE, BU.BUD_AMT,
TotalAppropriations = (SELECT SUM(A.APPR_AMT) FROM APPROPRIATIONS A WHERE A.PROJ_ID = PR.PROJ_ID AND A.APPR_DATE < BU.BUD_DATE),
TotalExpenses= (SELECT SUM(E.EXP_AMT) FROM EXPENDITURES E WHERE E.PROJ_ID = PR.PROJ_ID AND E.EXP_DATE < BU.BUD_DATE)
FROM PROJECTS PR
INNER JOIN BUDGETS BU ON BU.PROJ_ID = PR.PROJ_ID
``````

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
This should also work,

select p.id, p.name, b.bud_date, b.bud_amt,
a.sum_app, e.sum_exp
from projects p left join budgets b on p.id = b.proj_id
left join
(SELECT proj_id, sum(appr_amt) as sum_app
FROM APPROPRIATIONS
group by proj_id) a on p.id=a.proj_id
left join
(SELECT proj_id, sum(exp_amt) as sum_exp
FROM EXPENDITURES
group by proj_id) e on p.id=e.proj_id

Commented:
mikainz, your query wont be accurate as the Total Appropriations and Expenditures have to be 'prior to budget date'

Commented:
sorry I missed that

Commented:
try this
select p.id, p.name, b.bud_date, b.bud_amt,
a.sum_app, e.sum_exp
from projects p left join budgets b on p.id = b.proj_id
left join
(SELECT a1.proj_id, sum(a1.appr_amt) as sum_app
FROM APPROPRIATIONS a1 left join budgets b1
where a1.appr_date < coalesce(b1.bud_date,convert(datetime,'21000101'))
group by a1.proj_id) a on p.id=a.proj_id
left join
(SELECT e1.proj_id, sum(e1.exp_amt) as sum_exp
FROM EXPENDITURES e1 left join budgets b1
where e1.exp_date < coalesce(b1.bud_date,convert(datetime,'21000101'))
group by e1.proj_id) e on p.id=e.proj_id

Commented:
Many thanks!
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile