Ajuda com query

[Rômulo_SD]

Oi pessoal

estou com dificuldade para montar esta query que o teste esta pedindo

I am having a little trouble finding the best candidate based on test results. For each answered question I decided for a score (0, 0.5 or 1.0). I have three tables in the database: candidate, test and question_score. The candidate table has two columns: candidate_id and name. The test table has 2 columns: test_id and name. The question_score table has 4 columns: question_id, candidate_id, test_id and score, with candidate_id being a FK to the candidate table and test_id is a FK to the test table. I need a SQL query that return the candidate name and his total score, ordered by the best candidate (bigger score) to the worst, for all candidates that answered the test named "Java backend". *

 

tentei fazer assim

select candidate.name cn 
from candidate 
inner join question_score on (candidate.id == question_score.cadidate_id)
GROUP BY SUM(score) AS 'Best Candidate Results';

 

mas esta errado

 

alguém ajuda?

[Leonardo Persan]

Tenta assim:

SELECT c.name, SUM(q.score) as score
FROM candidate c
INNER JOIN question_score q ON c.candidate_id = q.candidate_id
INNER JOIN test t ON t.test_id = q.test_id and t.name = "Java backend"
GROUP BY c.candidate_id
ORDER BY SUM(q.score);