May I ask where did I go wrong with my SQL query?

Question

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 :(

Answer 1

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 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;