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:

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

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

thanks

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

Ahaa, that's how I should do it.

Thank you very much man, it works perfectly

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.