英文:
May I ask where did I go wrong with my SQL query?
问题
以下是翻译好的部分：
问题：
我的查询：
SELECT
first_name,
last_name,
ROUND(AVG(score), 2) AS avg_score
FROM students
WHERE test_name IN ('English Test', 'History Test')
AND score >= 0.9
GROUP BY id, first_name, last_name
ORDER BY avg_score DESC;
输出：
谢谢！
为了获得学生的平均分，但我一直遇到错误 
英文:
Question:
My Query:
SELECT
first_name,
last_name,
ROUND(AVG(score), 2) AS avg_score
FROM students
WHERE test_name IN ('English Test', 'History Test')
AND score >= 0.9
GROUP BY id, first_name, last_name
ORDER BY avg_score DESC;
The Output:
Thanks!
To get the average os the students but I keep on getting an error
答案1
得分: 0
不应在GROUP BY中包括id。因为每行的id是唯一的，所以您不应该将每个学生的所有分数合并在一起。
此外，问题中说您应该返回平均分至少为0.9的学生。您正在过滤原始分数，而不是平均分数。请使用HAVING来过滤聚合结果。参考 https://stackoverflow.com/questions/9253244/sql-having-vs-where
说明中要求按未四舍五入的值排序，所以请使用ORDER BY AVG(score) DESC而不是ORDER BY avg_score。说明中没有提到0.9的检查应该基于四舍五入的值还是未四舍五入的值；在下面的代码中，我使用了未四舍五入的值。
SELECT
first_name,
last_name,
ROUND(AVG(score), 2) AS avg_score
FROM students
WHERE test_name IN ('English Test', 'History Test')
GROUP BY first_name, last_name
HAVING AVG(score) >= 0.9
ORDER BY AVG(score) DESC;
英文:
You should not include id in the GROUP BY. That's unique to each row, so you're not combining all the scores for each student.
Also, the question says you should return the student's whose average score is at least 0.9. You're filtering the raw scores, not the average. Use HAVING to filter the aggregated results. See https://stackoverflow.com/questions/9253244/sql-having-vs-where
The instructions say that the ordering should be by the unrounded value, so use ORDER BY AVG(score) DESC rather than ORDER BY avg_score. It doesn't mention whether the 0.9 check should be on the rounded or unrounded value; in my code below I use the unrounded value.
SELECT
first_name,
last_name,
ROUND(AVG(score), 2) AS avg_score
FROM students
WHERE test_name IN ('English Test', 'History Test')
GROUP BY first_name, last_name
HAVING AVG(score) >= 0.9
ORDER BY AVG(score) DESC;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论