Currently I am developing a system to support a competition. In this competition there are 11 judges that give a grade to the participant. This grade is stored in a table per judge. With the following Select statement I retrieve the total score from the `MySQL database` (and also some `inner joins` to `select` other information from other tables).

Now I want to have ranking in this system, where also ex aequo is taken into account. I have tried several `mysql` solutions from this forum. But somehow the statement is always incorrect. Hopefully, someone can help me.

```
SELECT optreden.id, optreden.wedstrijd, optreden.jaartal, 
liedje.titel, club.volledige_clubnaam,
(SELECT SUM(score.score_lied) FROM score WHERE score.optreden=optreden.id) AS score
FROM optreden
INNER JOIN liedje ON optreden.liedje=liedje.id
INNER JOIN club ON liedje.uitvoerende_club=club.id
WHERE optreden.wedstrijd="voorselectie" AND optreden.jaartal=2014
GROUP BY optreden.id
ORDER BY score_lied DESC

```

With this query, I get the following result:

```
+----+--------------+------+----------+--------------+------+
| id | Competition  | Year | Title    | Artist (club)| Score|
+----+--------------+------+----------+--------------+------+
| 12 | voorselectie | 2014 | Song 1   | Club 1       | 792  |
| 16 | voorselectie | 2014 | Song 2   | Club 2       | 600  |
| 10 | voorselectie | 2014 | Song 3   | Club 3       | 600  |
| 11 | voorselectie | 2014 | Song 3   | Club 3       | 500  |
| 12 | voorselectie | 2014 | Song 3   | Club 3       | 400  |

```

The query fetches information from the following tables:

* Score: in this table the scores given by the 11 judges is stored as
line-items (result is given below);
* Optreden: in this table the information from the club and the song is linked;
* Club: information from the club,
such as name;
* Liedje: information about the song, performed by the
club.
All the tables are linked by id's. In the result, there should come a column, with the ranking of the club.

The result from the table score is as follows:

```
+----+----+--------+----+
| ID | Pid| Userid | 85 |
+----+----+--------+----+
| 1  | 12 | 444    | 85 |
| 12 | 12 | 454    | 92 |
| 13 | 12 | 445    | 87 |
| 14 | 12 | 446    | 56 |
| 15 | 12 | 447    | 81 |
| 16 | 12 | 448    | 78 |
| 17 | 12 | 449    | 55 |
| 18 | 12 | 450    | 69 |
| 19 | 12 | 451    | 88 |
| 20 | 12 | 452    | 69 |
| 21 | 12 | 453    | 32 |
+----+----+--------+----+

```

Where the columns represent the following:
- ID: the `id` of the score in the table;
- Pid: the `id` of the performance (with this performing club and song are linked);
- Userid: the `userid` of the 11 judges;
- Score: the `score` given by each judge.

The end result should be as follows:

```
+----+--------------+------+----------+--------------+------+------+
| id | Competition  | Year | Title    | Artist (club)| Score| Rank |
+----+--------------+------+----------+--------------+------+------+
| 12 | voorselectie | 2014 | Song 1   | Club 1       | 792  | 1    |
| 16 | voorselectie | 2014 | Song 2   | Club 2       | 600  | 2    |
| 10 | voorselectie | 2014 | Song 3   | Club 3       | 600  | 2    |
| 11 | voorselectie | 2014 | Song 3   | Club 3       | 500  | 4    |
| 12 | voorselectie | 2014 | Song 3   | Club 3       | 400  | 5    |

```

I have tried the solution from Neville before and now again. I have rewritten the `SQL statement` as follows:

```
SELECT optreden.id, optreden.wedstrijd, optreden.jaartal, 
liedje.titel, club.volledige_clubnaam,
(SELECT SUM(score.score_lied) FROM score WHERE score.optreden=optreden.id) AS score, CASE
WHEN @prev_value = score THEN @rank_count
WHEN @prev_value := score THEN @rank_count := @rank_count + 1
END AS rank,
FROM optreden
INNER JOIN liedje ON optreden.liedje=liedje.id
INNER JOIN club ON liedje.uitvoerende_club=club.id
WHERE optreden.wedstrijd="voorselectie" AND optreden.jaartal=2014
GROUP BY optreden.id
ORDER BY score_lied DESC

```

Here I get the error: `Unknown column 'score' in 'field list'.`.