Sql | SoloLearn: Learn to code for FREE!

+1

# Sql

I have 3 matrix Athletes(idathletes, name, age, country) Coach(idcoach, name, age, country) Training(idtraining, salary, athletes_idathletes, coach_idcoach)... I want to show the max sum of the coach and his name.... One coach can train more than one athletes

5/19/2020 9:07:49 PM

Jason Papastavrou

10 Answers

New Answer

+3

SELECT c.`name`, sum(t.`salary`) AS 'balance' FROM `Coach` c JOIN `Training` t ON t.`coach_idcoach` = c.`id` WHERE c.`idcoach` = 1 change the id in last line

+1

SELECT c.name, sum(t.salary) "sum_salary" FROM Coach as c JOIN Training as t ON c.idcoach = t.coach_idcoach GROUP BY c.name ORDER BY "sum_salary" DESC; The first result in output will have Max salary.

0

This prints me the sum of all coaches... I want only the sum of one coach

0

It prints the sum of only 1 coach(idcoach = 1). Jason Papastavrou Try it and see for yourself

0

Yeah you are rigtht... But if i dont know in which idcoach place is he... What should i do?

0

Jason Papastavrou You have to know the id of the coach you are searching for because idcoach is the primary key

0

Ok and one last question, if i want to show the names of the athletes he trains

0

You can chain different JOIN statements SELECT c.`name` AS 'Coach', group_concat(a.`name`) AS 'Athletes' sum(t.`salary`) AS 'balance' FROM `Training` t JOIN `Coach` c ON t.`coach_idcoach` = c.`idcoach` JOIN `Athletes` a ON t.`athletes_idathletes` = a.`idathletes` WHERE c.`id` = 1

0

You are the best

0

Jason Papastavrou thanks