Oracle GROUP BY clause is having issue

SQL Query in oracle database is:

SELECT max(employee_id)
from bank_data
group by job_type
having job in ('Engineer','Artist');

My table data is:

I get below error:

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

The explanation of this exception says when query doesn’t contain all expression, but why should I include extra things if I don’t want it?

Answer

This exception comes when your GROUP BY condition is missing some column(s). In your case, you missed one column.

Internal Working & Root Cause:

For executing this query, the SQL engine first computes the data set. Here only single projection(column) is present viz. employee_id. After obtaining this initial data set the SQL engine further tries to cluster the records i.e. groups them on the basis of job_type column to honor the GROUP BY clause in your query. At this point your query runs into the exception because job_type column is not present in your result set.

Moreover, your GROUP BY clause expects the selected columns only and their order defines their priorities while grouping i.e. the column mentioned will get highest priority and the one in last would have least.

Solution:

Modify your query as follows-

Select job_type, job, max(employee_id) from bank_data group by job_type, job having job in ('Engineer' , 'Artist');

This will remove that exception(Kindly adjust the order of columns in the GROUP BY clause as per the desired priorities.

Certainly, you need to filter out only the relevant projections(Aliases are helpful for this) in your final result-set and to achieve that you need to nest this query inside an outer query .

So your final query would be something like:

Select my_col from (select job_type, job, max(employee_id) as my_col from bank_data group by job_type, job having job in ('Engineer', 'Artist'));

Note:

Since Oracle(including others like MySQL, PostgreSQL, DB2 etc) fall under RDBMS(Relational Database Management System) there might not be a direct query to fetch desired data so you must analyze the requirements with the help of TRC(Tuple Relational Calculus) & then you can created the relevant nested query on the basis of the obtained TRC expression(It is very helpful in creating complex queries).