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

Question

Question:

This is the 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:

error, expected 11 rows expected

Thanks!

To get the average os the students but I keep on getting an error :(

`GROUP BY id, first_name, last_name` should just be `GROUP BY first_name, last_name`. `id` is unique to each row so you're not combining all the scores for each student. — Barmar, Aug 10 '23 at 20:08
you should include the error in your question. @Barmar's comment seems correct though — Bret Weinraub, Aug 10 '23 at 20:10
You must check calculated average score, not separate scores. — Akina, Aug 10 '23 at 20:12
I am color blind :) try to include the error in the text description :) — Bret Weinraub, Aug 11 '23 at 16:23

Barmar · Accepted Answer · 2023-08-10T23:07:20.663

0

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;

edited Aug 10 '23 at 23:07

answered Aug 10 '23 at 20:12

Barmar

741,623
53
500
612

This needs to be `HAVING avg(score) >= 0.9`; as is you will include students with averages >= .085 – ysth Aug 10 '23 at 22:58
Why would that happen? `0.085` rounds to `0.08`, which is not `>= 0.90` – Barmar Aug 10 '23 at 23:03
I guess you meant to use something like `0.899` as the example. – Barmar Aug 10 '23 at 23:05
The instructions aren't clear about whether this check should be on rounded or unrounded values, but since they say to use unrounded for ordering, I guess we can assume the same for HAVING. – Barmar Aug 10 '23 at 23:08
round doesn't do banker's rounding https://dbfiddle.uk/PHnD0W8N I see I did get the decimal place wrong – ysth Aug 11 '23 at 02:08
1

@ysth It uses banker's rounding for `FLOAT`, round away from 0 for `DECIMAL`. See https://dev.mysql.com/doc/refman/8.0/en/precision-math-rounding.html – Barmar Aug 11 '23 at 04:23
Sorry for the late response. Thank you so much! This much more make sense. :) – Mariz Papas Aug 14 '23 at 16:37

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

1 Answers1