ERROR IN SQL QUERY for group by

Question

I want to select average salary by department number and age, I used this query

SELECT 
    DEPARTMENT_NUMBER,
    ROUND((MONTHS_BETWEEN(SYSDATE, BIRTHDAY)) / 12) AS AGE,
    AVG(SALARY)
FROM 
    EMPLOYEE 
GROUP BY 
    DEPARTMENT_NUMBER, AGE;

but I am getting error:

ORA-00904: "AGE": invalid identifier

Tagged Oracle . . . Oracle errors come from the Oracle database. — Gordon Linoff
– Gordon Linoff, Commented Sep 2, 2014 at 17:37

Gordon Linoff · Accepted Answer · 2014-09-02 17:36:56Z

5

You cannot use a column alias in the group by in Oracle. Just use a subquery:

SELECT DEPARTMENT_NUMBER, AGE, AVG(SALARY)
FROM (SELECT e.*, ROUND((MONTHS_BETWEEN(SYSDATE,BIRTHDAY))/12) AS AGE
      FROM EMPLOYEE
     ) e
GROUP BY DEPARTMENT_NUMBER, AGE;

answered Sep 2, 2014 at 17:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeff Anderson · Accepted Answer · 2014-09-02 17:37:46Z

4

You cannot group by an alias, try this:

    SELECT DEPARTMENT_NUMBER
           ,ROUND((MONTHS_BETWEEN(SYSDATE,BIRTHDAY))/12) AS AGE
           ,AVG(SALARY)
    FROM EMPLOYEE 
    GROUP BY DEPARTMENT_NUMBER, ROUND((MONTHS_BETWEEN(SYSDATE,BIRTHDAY))/12);

answered Sep 2, 2014 at 17:37

Jeff Anderson

8198 silver badges21 bronze badges

Collectives™ on Stack Overflow

ERROR IN SQL QUERY for group by

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related