what should i do to make this SQL query work?-CodePudding

SELECT s.Name, (
COUNT(*) FROM (SELECT 1 FROM pruefen p WHERE p.MatrNr = s.MatrNr AND p.Note < 5) / 
COUNT(*) FROM (SELECT 1 FROM pruefen p WHERE p.MatrNr = s.MatrNr)
) as Anteil FROM Studenten s

ORDER BY Anteil DESC, s.Name ASC
LIMIT 10;

CodePudding user response：

I'm quite sure this query will work; meaning that it won't return an error but I'm not certain if it will return correct result as what you intended:

SELECT s.Name, 
       SUM(p.Note=5)/COUNT(*) as Anteil 
FROM Studenten s
JOIN pruefen p ON p.MatrNr = s.MatrNr 
GROUP BY s.Name
ORDER BY Anteil DESC, s.Name ASC;

If this doesn't return the result you're looking for, update your question with data samples and expected output.

CodePudding user response：

SELECT s.Name, ((SELECT COUNT() FROM pruefen p WHERE p.MatrNr = s.MatrNr AND p.Note < 5) / (SELECT COUNT() FROM pruefen p WHERE p.MatrNr = s.MatrNr) ) as Anteil FROM Studenten s ORDER BY Anteil DESC, s.Name ASC LIMIT 10;