I have 3 tables and followinn entries:
*users [id, name]*
insert into users (1, 'jack')
*portfolios [id, user_id, status]*
insert into portfolios (1, 1, 'active')
insert into portfolios (2, 1, 'active')
*deposits [id, portfolio_id, amount]*
insert into deposits (1, 1, 10)
insert into deposits (2, 2, 5)
*users [id, name]*
insert into users (2, 'jill')
*portfolios [id, user_id, status]*
insert into portfolios (3, 2, 'active')
insert into portfolios (4, 2, 'pending')
insert into deposits (3, 3, 3)
insert into deposits (4, 4, 4)
I need to create a query that will show the total amount for each user, where the connected portoflio is "active. Basically, according to inserts above, I should have:
name: 'jack', total_amount: 15
name: 'jill', total_amount: 3
Do I need to use aggregation?
>Solution :
You should join, preferably with explicit joins, filter for active portfolio.status aggregate on users.name and sum deposits.amount
select
users.name, sum(deposits.amount) as total_amount /*each distinct user and the sum for their amount*/
from users
inner join portfolio on users.id = portfolio.user_id /*use explicit joins on corresponding ids*/
inner join deposits on portfolio.id = deposits.portfolio_id
where portfolio.status = 'active' /*filter for active only*/
group by users.name /*this gives unique list of users*/