How to sum values for specific user portfolios with aggregation-CodePudding

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?

CodePudding user response：

the query should be

SELECT u.name, SUM(d.amount) as total_amount FROM users u, deposits d, portfolios p
 WHERE u.id=p.user_id and p.id=d.portfolio_id and p.status='active'
 GROUP BY  u.id

CodePudding user response：

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*/