Error: ORA-00937

Question

I want to create a table NEW_DEPTS. I have DEPARTMENT_ID and DEPARTMENT_NAME which is in DEPARTMENTS table.

            desc departments
            Name                            Null?    Type
            ------------------------------- -------- ----
            DEPARTMENT_ID                   NOT NULL NUMBER(4)
            DEPARTMENT_NAME                 NOT NULL VARCHAR2(30)
            MANAGER_ID                               NUMBER(6)
            LOCATION_ID                              NUMBER(4)

My EMPLOYEES table contains:

            desc employees
            Name                            Null?    Type
            ------------------------------- -------- ----
            EMPLOYEE_ID                     NOT NULL NUMBER(6)
            FIRST_NAME                               VARCHAR2(20)
            LAST_NAME                       NOT NULL VARCHAR2(25)
            EMAIL                           NOT NULL VARCHAR2(25)
            PHONE_NUMBER                             VARCHAR2(20)
            HIRE_DATE                       NOT NULL DATE
            JOB_ID                          NOT NULL VARCHAR2(10)
            SALARY                                   NUMBER(8,2)
            COMMISSION_PCT                           NUMBER(2,2)
            MANAGER_ID                               NUMBER(6)
            DEPARTMENT_ID                            NUMBER(4)

Now, I want to create a table NEW_DEPTS which should contain DEPARTMENT_ID and DEPARTMENT_NAME along with total SALARY that each Department has. Every department have certain number of employees, so, for department number 10, 20, 30, and so on, total Salary should be calculated.

My main query is:

           create table new_depts as
           select d.department_id,d.department_name,sum(e.salary) dept_sal
           from employees e,departments d
           where e.department_id = d.department_id;

I got an Error:

           select d.department_id,d.department_name,sum(e.salary) dept_sal
              *
           ERROR at line 2:
           ORA-00937: not a single-group group function

So, I googled it. I found that group function like sum, avg, max, min can't be used in select statement. I needed GROUP BY clause. So, I wanted to solve it individually. Therefore, I skipped the "create table..." statement and wrote:

           select distinct d.department_id,d.department_name,sum(e.salary) dept_sal
           from employees e,departments d
           where e.department_id = d.department_id
           group by department_id;

I got Error:

           group by department_id;
                  *
           ERROR at line 4:
           ORA-00911: invalid character

Then I wrote:

           select d.department_id,d.department_name,sum(e.salary) dept_sal
           from employees e,departments d
           where e.department_id = d.department_id
           group by d.department_id;

I got Error:

           select d.department_id,d.department_name,sum(e.salary) dept_sal
                   *
           ERROR at line 1:
           ORA-00979: not a GROUP BY expression

Then I wrote:

           select d.department_id,d.department_name,sum(e.salary) dept_sal
           from employees e,departments d
           where e.department_id = d.department_id
           group by department_id;

I got Error:

           group by department_id
                   *
           ERROR at line 4:
           ORA-00918: column ambiguously defined

I know that its a "join". But not getting how should I place GROUP BY clause so that my GROUP FUNCTION may run correctly.

`GROUP BY department_id` is ambiguous. You need to use your table alias. Try `GROUP BY d.department_id`. — Aiias, Mar 30 '13 at 04:20
possible duplicate of [ORA-00979 not a group by expression](http://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression) — Ben, Aug 29 '14 at 10:55

score 1 · Accepted Answer · answered Mar 30 '13 at 04:29

You need to group by all columns not in an aggregate function and since you have defined a table alias you must use that in your columns. The error ORA-00918: column ambiguously defined is because you have two columns named department_id so you must specify the table the department_id is from:

select d.department_id,
  d.department_name,
  sum(e.salary) dept_sal
from employees e
inner join departments d
  on e.department_id = d.department_id
group by d.department_id, d.department_name;

You will notice that I altered the query to use ANSI JOIN syntax (INNER JOIN).

Yes, just now I solved it by myself using the same code as you mentioned. Thanks. — ankitaP, Mar 30 '13 at 04:33

Error: ORA-00937

1 Answers1