2
SELECT ssn, fname, minit, lname, AVG(hours)
FROM EMPLOYEE, WORKS_ON
WHERE EMPLOYEE.ssn = WORKS_ON.essn
GROUP BY hours
ORDER BY AVG(hours) DESC

It shows error of ORA-00979: not a GROUP BY expression

I wonder why its not working? Btw, all table exist and free of any errors. Its in Oracle AE 11g ===========UPDATE========================== The table as requested

SSN FNAME   MINIT   LNAME   AVG(HOURS)
888665555   James   E   Borg    -
666884444   Ramesh  K   Narayan 40
999887777   Alicia  J   Zelaya  20
987987987   Ahmad   V   Jabbar  20
453453453   Joyce   A   English 20
123456789   John    B   Smith   20
987654321   Jennifer    S   Wallace 17.5
333445555   Franklin    T   Wong    7.33333333333333333333333333333333333333
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
weia design
  • 1,250
  • 2
  • 14
  • 22
  • You seem to have pasted the wrong thing into your question: you meant to post details about your tables (including `salary`), but instead you posted the result-set produced by the corrected query (which does not include `salary`). – ruakh Mar 20 '13 at 21:45
  • disregard my previous request :) thanks! – weia design Mar 21 '13 at 02:33
  • Possible duplicate of [ORA-00979 not a group by expression](https://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression) – Ben Dec 10 '17 at 08:12

3 Answers3

3

I think you meant to GROUP BY the aggregated columns:

SELECT ssn, fname, minit, lname, AVG(hours)
FROM EMPLOYEE, WORKS_ON
WHERE EMPLOYEE.ssn = WORKS_ON.essn
GROUP BY ssn, fname, minit, lname
ORDER BY AVG(hours) DESC
Kermit
  • 33,827
  • 13
  • 85
  • 121
  • Whatever your date column is, you would add `TO_CHAR(dateColumn, 'YYYY')` to both your column list and your `GROUP BY` list. – Kermit Mar 20 '13 at 21:34
3

I think you're grouping by the exact opposite of what you need. You want:

SELECT ssn, fname, minit, lname, AVG(hours)
FROM EMPLOYEE, WORKS_ON
WHERE EMPLOYEE.ssn = WORKS_ON.essn
GROUP BY ssn, fname, minit, lname
ORDER BY AVG(hours) DESC
ruakh
  • 175,680
  • 26
  • 273
  • 307
  • btw, what if I want to show salary for multiple years(more than once in the DB) this query result would be outstanding....How can I do it? Thanks! – weia design Mar 20 '13 at 21:18
2

Since you're using an aggregation function AVG(hours), you need to group the rest of the results:

SELECT ssn, fname, minit, lname, AVG(hours)
FROM EMPLOYEE, WORKS_ON
WHERE EMPLOYEE.ssn = WORKS_ON.essn
GROUP BY ssn, fname, minit, lname
ORDER BY AVG(hours) DESC
clav
  • 4,221
  • 30
  • 43