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    |

The "total sum for each team" you mean the total number of competitions that competitors in a team participated or the number of competitors in a team? Although I doubt it would be the latter given that you have 3 columns of competitors in your teams table. Or probably you mean the total result for each group? — Edper
– Edper, Commented Jun 8, 2013 at 4:33
Is it ok to redesign your database like instead of Teams with competitor it should simply be TeamID and TeamName on Team table and then another Table for Competitors with CompetitorID and TeamID in that way that would be the easiest to query your database. Besides it is more flexible to add members or delete members from a team. — Edper
– Edper, Commented Jun 8, 2013 at 4:38
Yes, detailed suggestions of redesign are welcome. I would appreciate very much if you can give my any example queries using that solution. — peltors
– peltors, Commented Jun 8, 2013 at 4:45
any time you see numbered columns, you know yo're in trouble - you design is not 3NF: Create a child table to hold the results. — Bohemian
– Bohemian ♦, Commented Jun 8, 2013 at 5:48

Edper · Accepted Answer · 2013-06-08 05:46:41Z

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

answered Jun 8, 2013 at 5:46

Edper

9,3621 gold badge32 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problems with subquery in MySql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related