Problems with subquery in MySql

Question

I'm stuck with a problem in MySql, please help me.

In this example I have two tables, one with the results of a bunch of competitors and one that defines which three competitors that makes a team. In reality I have a number of other tables as well, but they are not really needed to describe this problem.

Table with results for each competitor
| competitor_id | result1 | result2 | result3 | result4 |
|       1       |    1    |    1    |    1    |     1   |
|       2       |    1    |    2    |    2    |     1   |
|       3       |    2    |    3    |    2    |     1   |
|       4       |    1    |    5    |    3    |     2   |
|       5       |    4    |    3    |    2    |     3   |
|       6       |    3    |    2    |    1    |     2   |
|       7       |    2    |    1    |    4    |     2   |
|       8       |    2    |    1    |    2    |     1   |
|       9       |    1    |    2    |    3    |     2   |

Table showing teams
| team_id | competitor1 | competitor3 | competitor3 |
|    1    |      1      |       3     |       4     |
|    2    |      2      |       8     |       9     |
|    3    |      7      |       6     |       5     |

I would now like to create a query that gives me the total sum of each team. I need to have it i one query (maybe with subqueries) because I need to sort desc on the total result.

In other words, I need a result set giving me team.id sorted desc on the total result of each team.

Anyone?

EDIT: Here's an update showing the desired result

First, let´s sum the results of each competitor:

Competitor 1: 1+1+1+1=4
Competitor 2: 1+2+2+1=6
Competitor 3: 2+3+2+1=8
Competitor 4: 1+5+3+2=11
Competitor 5: 4+3+2+3=12
Competitor 6: 3+2+1+2=8
Competitor 7: 2+1+4+2=9
Competitor 8: 2+1+2+1=6
Competitor 9: 1+2+3+2=8

Then let's look at the team table.

Team 1 consists of competitors 1, 3 and 4.  
Team 2 consists of competitors 2, 8 and 9.  
Team 3 consists of competitors 7, 6 and 5.  

Total sum of team with id = 1 is 4+8+11=23  
Total sum of team with id = 2 is 6+6+8=20  
Total sum of team with id = 3 is 9+8+12=29  

Given all of this, I would like my result set to be

| id | team_sum |
| 3  |    29    |
| 1  |    23    |
| 2  |    20    |

Answer 1

Why not redesign your database like you have only two tables one for competitors and one for team like :

Competitors Table:
`competitor_id`, `team_id`, `result1`, `result2`, `result3`, `result4`

Team Table:
`team_id`, `team_name`

And your query would be very easy like:

SELECT A.team_id, B.team_name, SUM(result1+result2+result3+result4) as TotalResult
FROM competitors A
INNER JOIN team B
ON A.team_id=B.team_id
GROUP BY A.team_id, B.team_name

See my fiddle demo