MS SQL

aggregate on multiple joins from one parent table

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Nov 2007

Posts: 8

Reputation: AbuShokry is an unknown quantity at this point

Solved Threads: 0

AbuShokry

Offline

Newbie Poster

aggregate on multiple joins from one parent table

Mar 17th, 2009

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:

Help with Code Tags

(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

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 138

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: aggregate on multiple joins from one parent table

Mar 17th, 2009

 Help with Code Tags 
 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