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

tchris
tchris asked
on
Medium Priority
689 Views
Last Modified: 2013-12-07
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

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

Open in new window

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

Ask the Experts

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

Author

Commented:
Many thanks!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.