I have three tables:
Teams:
- ID (int,unq)
- Tname (text)
Users:
- ID (int),
- TeamID (int,unq)
- Uname (text)
Deposits:
- UserID (int)
- Depos (double)
Each user may have few deposits.
I need the summary result like this where there will be quantity of users in each team and sum of their deposits:
TeamID | User’sCount | SumOfDepos |
---|---|---|
1 | 10 | 102.0 |
2 | 12 | 124.0 |
I tried to add teamid together with userid in Deposits table and execute this:
select ucnt.teamid, ucnt.cnt as ucount,sum(depos) from deposits inner join teams on teams.id=deposits.teamid inner join (select teamid,count(*) as cnt from deposits group by teamid having count(*)>=1) as ucnt on deposits.teamid=ucnt.teamid GROUP by ucnt.teamid,ucnt.cnt;
but I have quantity of deposits, not quantity of users in each team
Thanks in advance for your support!