Oracle – Resolving ORA-00979: Not a GROUP BY Expression

oracle

I'm getting the error: 'ORA-00979: not a group by expression' when I try to run this query.

select empno, empname
from emp.employee
group by empno, empname
having empmsal > avg(empmsal);

I'm attempting to find employees with a salary above the average.
Can you see anything wrong with the query?

Sorry if this is obvious. I'm new to sql.

Best Answer

Because the group by on empno and empname has basically no point (supposing they're unique in the table), a better way to do this would be:

select empno, empname
from emp.employee
where empmsal > (select avg(empmsal) from emp.employee)

The thing is that (select avg(empmsal) from emp.employee) is computed only once - appearing in the where condition, and unless you have an index on empmsal it would perform a table scan, and then another for the main query. That's as best as it could get.

Another way is using window functions:

with cte as
( select empno, empname,
         avg(empmsal) over () as avg_empsal 
  from emp.employee
) 
select empno, empname
from cte
where empmsal > avg_empmsal ;

Related Solutions

Oracle Group By – Nested Query with Group By and Having Clauses

If I understand correctly:

select *
  from (SELECT D.DID, AVG(F.DIST) as AvgDist 
        from Destinations D 
        join Flights F ON F.DID = D.DID 
        GROUP BY D.DID) avgdist
  join
       (SELECT F.AID,F.DID, SUM(F.DIST) as fDist 
        from Flights F 
        join Destinations D ON F.DID = D.DID 
        GROUP BY F.AID,F.DID) sumdist
        on avgdist.did = sumdist.did
  where avgdist.avgdist < sumdist.fDist;

Oracle – Lookup Multiple IDs in Separate Table with 2 Subqueries

You're permitted to do multiple joins in a single statement. You can do this even when joining on the same table by using aliases.

I created a very quick example which is similar to your case. I just used ids instead of a time, and I stored the whole name as a single field; you'll just have to concatenate the first and last name on your own ;)

Schema and Data

CREATE TABLE prtimesheet (
  id numeric,
  submitter_id numeric,
  approver_id numeric
);

 CREATE TABLE srm_resources (
   person_id numeric,
   name text
 );

 INSERT INTO prtimesheet VALUES(1, 1000, 1001), (2,1000,1002), (3,1002, 1001), (4,1001,1002);

 INSERT INTO srm_resources VALUES (1000,'Jack Ryan'), (1001, 'Bart Mancuso'), (1002, 'James Greer');

Multiple joins on same table using aliases

SELECT prtimesheet.id, sub_table.name AS submitter_name, app_table.name AS approver_name
FROM prtimesheet
JOIN srm_resources AS sub_table
ON prtimesheet.submitter_id = sub_table.person_id
JOIN srm_resources AS app_table
ON prtimesheet.approver_id = app_table.person_id
ORDER BY id;

This should fix you right up!

Here is a SQL Fiddle for you to play around with if you like.

Edit: And as suggested...

The Oracle version: Schema and Data

CREATE TABLE prtimesheet (
  id number,
  submitter_id number,
  approver_id numeric
 );

  CREATE TABLE srm_resources (
     person_id number,
     name varchar(25)
  );

  INSERT ALL
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(1, 1000, 1001) 
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(2,1000,1002)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(3,1002, 1001)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(4,1001,1002)
  SELECT * FROM DUAL;

 INSERT  ALL
 INTO srm_resources(person_id, name) VALUES(1000,'Jack Ryan') 
 INTO srm_resources(person_id, name) VALUES(1001, 'Bart Mancuso')
 INTO srm_resources(person_id, name) VALUES(1002, 'James Greer')
 SELECT * FROM DUAL;

The Oracle version: Joins and aliases

SELECT prtimesheet.id, sub_table.name AS submitter_name, app_table.name AS approver_name
FROM prtimesheet
JOIN srm_resources sub_table
ON prtimesheet.submitter_id = sub_table.person_id
JOIN srm_resources app_table
ON prtimesheet.approver_id = app_table.person_id
ORDER BY id;

Here is the Oracle SQL Fiddle!