aggregate on multiple joins from one parent table

Expand Post »

Hello all,

I'm having the following problem,

I have three tables, Person, Income, Outcome
Person has the primary key : ID
Income has two fields : PersonID [Foreign] and IncomeAmount
Outcome has two fields : PersonID [foreign] and OutcomeAmount

I need to make a query that gets the summation of IncomeAmount and the summation of OutcomeAmount for each personID

My Query which gets totally wrong data is:

(Toggle Plain Text)

SELECT Person.ID, Sum(Income.IncomeAmount) , Sum(Outcome.OutcomeAmount) 
From Person,Outcome,Income
Where Person.ID = Income.PersonID AND Person.ID = Outcome.PersonID
Group by Person.ID

Why does it gets wrong data?
what am I missing here ?

thanks

AbuShokry

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Nov 2007

Permalink

Mar 17th, 2009

Re: aggregate on multiple joins from one parent table

MS SQL Syntax (Toggle Plain Text)
SELECT p.ID,
(SELECT SUM(i.IncomeAmount) FROM Income i WHERE i.PersonId = p.ID) AS INCOME_AMOUNT,
(SELECT SUM(o.OutcomeAmount) FROM Outcome o WHERE o.PersonId = p.ID) AS OUTCOME_AMOUNT
FROM Person p
select p.ID,
(SELECT SUM(i.IncomeAmount) from Income i where i.PersonId = p.ID) AS INCOME_AMOUNT,
(SELECT SUM(o.OutcomeAmount) from Outcome o where o.PersonId = p.ID) AS OUTCOME_AMOUNT
from Person p

Last edited by dickersonka; Mar 17th, 2009 at 4:36 pm.

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Mar 18th, 2009

Re: aggregate on multiple joins from one parent table

Ahaa, that's how I should do it.

Thank you very much man, it works perfectly

AbuShokry

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Nov 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: sql server remote access and user auithentication issue

Next Thread in MS SQL Forum Timeline: Report Server URL Not Vaild Error

DaniWeb Message

Cancel Changes

User Name
Password