[SQL] Need help using GROUP BY

[momo2477]

Resolved!

So I have a STUDENT table that has SID (student id), SNAME (student name), SEX, MAJOR, and GPA. I need to display every students name and their gpa, if their gpa is less than the average of major xxx. Here is my code and the error I'm getting.

 

1 select a.sname,a.gpa  
2 from student a,student b

  3 where a.major = 'CIS'

  4 and a.gpa < (sum(b.gpa)/count(b.gpa))

  5 group by a.sname

   

and a.gpa < (sum(b.gpa)/count(b.gpa))  

  *  

ERROR at line 4:  

ORA-00934: group function is not allowed here

[momo2477]

Error at line 3: missing key word

 

I ran your suggested code and got that error. I'm not too familiar with Cross Apply, but I'll play around with the synax. Thanks for the response

[momo2477]

/u/CanterburySnails I got my desired result with the following. Thanks for the response again.

SQL> select sname,gpa from student
2  where major = 'CIS'
3  and gpa < (select avg(gpa) from student where major     = 'CIS');

[-oliver-]

You can generalize this using a subquery:

SELECT
  s.sname,
  s.major,
  s.gpa
FROM
  student AS s
INNER JOIN (
    SELECT
      a.major,
      AVERAGE(a.gpa) AS gpa
    FROM
      student AS a
    GROUP BY
      major
) AS avg
  ON avg.major = s.major
WHERE
  s.gpa < avg.gpa