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?
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