2

I am trying show all the different companies for which students work. However only companies where more than four students are employed should be displayed.

This is what I have so far:

SELECT EMPLOYER, COUNT (STUDENT_ID)
FROM STUDENT
GROUP BY STUDENT_ID
HAVING COUNT (STUDENT_ID) >4;

I keep getting this message:

ERROR at line 1: 
ORA-00979: not a GROUP BY expression

I don't get it. I also tried this earlier: 

SELECT STUDENT.EMPLOYER, COUNT (STUDENT.STUDENT_ID)
FROM STUDENT
GROUP BY STUDENT.STUDENT_ID
HAVING COUNT (STUDENT.STUDENT_ID) >4;

but nothing seems to work. Any help is appreciated. I am on SQL*Plus if that helps.

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
Lost_in_SQL_wilderness
  • 111
  • 2
  • 3
  • 8

1 Answers1

5

Try: 

SELECT EMPLOYER, COUNT (STUDENT_ID)
FROM STUDENT
GROUP BY EMPLOYER
HAVING COUNT (STUDENT_ID) >4;

- this will return a list of all employers with more than 4 students.

When grouping or including aggregated fields, your select statement should only include fields that are either aggregated or included in the group by clause - in your existing select, you are including EMPLOYER in your select clause, but not grouping by it or aggregating it.