inner join with group by expression in oracle sql
group-by, inner-join, oracle, sql
Solution
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
Problem
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?