?
Solved

SQL Join with Aggregates problem

Posted on 2009-02-11
6
Medium Priority
?
672 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?
0
Comment
Question by:tchris
  • 3
  • 2
6 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 1000 total points
ID: 23613380
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

0
 
LVL 8

Expert Comment

by:mikainz
ID: 23613409
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
0
 
LVL 25

Expert Comment

by:reb73
ID: 23613533
mikainz, your query wont be accurate as the Total Appropriations and Expenditures have to be 'prior to budget date'
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 8

Expert Comment

by:mikainz
ID: 23613717
sorry I missed that
 
0
 
LVL 8

Expert Comment

by:mikainz
ID: 23613764
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
0
 
LVL 1

Author Closing Comment

by:tchris
ID: 31545633
Many thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question