Error with ORDER BY used with HAVING Clause

Question

I am trying to use some basic SQL functions. I need to get an average of some data and order it in descending order. The error I get is "group function is not allowed"

Table:

STUDENTS
-----------
ID
CLASS
GRADE
ROOM

SQL:

    SELECT ID, class, AVG(Grade) AS AvgGrade
      FROM Students
     GROUP BY AVG(Grade)
    HAVING AVG(Grade) >= 3.0
     ORDER BY AVG(Grade) DESC

I was told that ORDER BY cannot be used with the HAVING clause and I would need to repeat the function. Any help?

score 5 · Accepted Answer · answered Sep 19 '11 at 23:20

5

GROUP BY avg(Grade) doesn't make any sense.

The GROUP BY expression defines the groups that you want the aggregate applied to.

Presumably you need GROUP BY ID, class

answered Sep 19 '11 at 23:20

Martin Smith

438,706
87
741
845

score 4 · Answer 2 · answered Sep 19 '11 at 23:20

4

You cannot have avg(Grade) under GROUP BY.

In your example, you'd have to have: GROUP BY ID, class.

answered Sep 19 '11 at 23:20

Branko Dimitrijevic

50,809
10
93
167

score 0 · Answer 3 · answered Sep 21 '11 at 11:01

In Standard SQL, only AS clauses ("column aliases") from the SELECT clause are allowed in the ORDER BY clause i.e.

SELECT ID, class, AVG(Grade) AS AvgGrade
  FROM Students
 GROUP BY ID, class
HAVING AVG(Grade) >= 3.0
 ORDER BY AvgGrade DESC;

Not all SQL products faithfully implement Standards, of course, but the above should work in SQL Server, for example.

Error with ORDER BY used with HAVING Clause

3 Answers3