inner join with group by expression in oracle sql

Question

I am new to sql, any help is appreciated.

I have two tables, employees and jobs. employees contain a variable job_id (multiple employees can have the same job_ID). jobs contain variables job_id and job_title (one job_ID correspond to one job_title, this is the hr schema in oracle if you are interested).

I want the query to return: the job_title, job_ID and the number of people who have the same job_Id.

I tried the following code:

select j.job_title, e.job_ID, count(e.job_ID)
from employees e, jobs j
where e.job_id=j.job_id
group by e.job_Id

the error message is:

ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause:
*Action:
Error at Line: 83 Column: 8

Can you help me fix this?

Answer 1

The error message is a bit misleading. When you select a bunch of fields and an aggregate, you have to group by every field you select and only the fields you select. So your query has to be:

select j.job_title, e.job_ID, count(e.job_ID)
from employees e, jobs j
where e.job_id=j.job_id
group by e.job_Id, j.job_title