I have tried the following code but I just get the full table with all the names and sum values instead of one row with the max value:
SELECT stageName, max(total_salary)
FROM (
SELECT c.*, sum(p.dailySalary) as total_salary
from contender as c
left join participant as p
on (p.contender = c.idContender)
group by c.idContender ) b
group by stageName;
output:
Yellow Jesters 205
TikTok 3073
Teabags 947
Bobbleheads 11840
Reddit 1486
but I just need: Bobbleheads 11840
PS: Please suggest a solution WITHOUT using desc and limit
Here's a solution that should work on any version of MySQL 5.x, using no ORDER BY, LIMIT, window functions, views, or CTEs.
SELECT a.stagename, a.total_salary
FROM (
SELECT c.*, sum(p.dailySalary) as total_salary
from contender as c
left join participant as p
on (p.contender = c.idContender)
group by c.idContender ) AS a
LEFT OUTER JOIN (
SELECT c.*, sum(p.dailySalary) as total_salary
from contender as c
left join participant as p
on (p.contender = c.idContender)
group by c.idContender ) AS b
ON a.total_salary < b.total_salary
WHERE b.total_salary IS NULL;
Tested on MySQL 5.7.27.
Output:
+-------------+--------------+
| stagename | total_salary |
+-------------+--------------+
| Bobbleheads | 11840 |
+-------------+--------------+