+ 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
10 Réponses
+ 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