SQL -- had this come up in an interview

Question

I just had this interview question that I couldn't answer. What is wrong with this query in SQL?

SELECT subject_code, AVG (marks)
FROM students
WHERE AVG(marks) > 75
GROUP BY subject_code;

Illegal use of avg in where clause, use having instead. https://stackoverflow.com/q/1209364/3993662 — baao, May 29 '18 at 23:29

score 3 · Accepted Answer · answered May 29 '18 at 23:29

3

I think that you need a having clause if you are trying to apply an aggregate into logic.

So the correct answer would be the query should look like

SELECT subject_code, 
AVG (marks) 
FROM students 
GROUP BY subject_code
HAVING AVG(marks) > 75

answered May 29 '18 at 23:29

Ian-Fogelman

1 Answers1