CODEWITHSUNDEEP
sql
Shubham R
Shubham R

Reputation: 7644

Calculate the sum of group in a table and joining on other table sql

i have two tables in sql.

table_team

t_id  team_name
 1      ABC
 2      XYZ
 3      PQR

i have another table_player

p_id t_id   player_name  payment
 10    1           Sam          1000
 20    1           jon           500
 30    2           will          680
 40    1           bob           700
 50    3           rob           890

i want to get the team's total payment from these two tables.

i'e sum of payemnt of each player in a particular team, and for all teams.

i thought of trying

select t_id,team_name,sum(b.payment) as total_payment from table_team a
left join table_player b on a.t_id = b.t_id

Upvotes: 0

Views: 33

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522516

Join to a subquery which computes the total payment for each team:

SELECT t1.t_id, t1.team_name, t2.total_payment
FROM table_team t1
INNER JOIN
(
    SELECT t_id, SUM(payment) AS total_payment
    FROM table_player
    GROUP BY t_id
) t2
    ON t1.t_id = t2.t_id
-- WHERE t1.team_name IN ('ABC', 'XYZ')

Upvotes: 1

beejm
beejm

Reputation: 2481

select t_id,team_name,sum(isnull(b.payment,0)) as total_payment from table_team a
left join table_player b on a.t_id = b.t_id
group by t_id, team_name

Just add a group by and it should work

Upvotes: 1

Piotr Rogowski
Piotr Rogowski

Reputation: 3890

select t_id,team_name,sum(b.payment) as total_payment 
from table_team a
left join table_player b on a.t_id = b.t_id
group by t_id,team_name

Upvotes: 1

Related Questions