GROUP BY SQL error

Asked

Viewed 932 times

3

I have the following exercise:

  1. List the department code and name, newest and oldest admission date of each department, sort by department name.

Here’s what I did:

select d.department_id, d.department_name, max(jh.start_date), min(jh.start_date) from departments d
inner join job_history jh on jh.department_id = d.department_id
group by d.department_id order by d.department_name;

But give the error below, I like to know why it happens and how to solve.

ORA-00979: não é uma expressão GROUP BY
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Erro na linha: 45 Coluna: 25

inserir a descrição da imagem aqui

mysql sql database oracle

  • What’s your question? Providing only the statement doesn’t help much, edit the question and provide more details than you’re having trouble with.

  • That’s the problem, I made the answer based on the statement I put described. But since I’m a beginner I don’t know why group by is giving error. After some research I discovered that the resolution would be by group by but I do not understand the error...

  • Try grouping by all the fields you put in select, at least it’s how most banks work.

  • Gave the following error:ORA-00934: group function is not allowed here 00934. 00000 - "group Function is not allowed here" *Cause: *Action: Line error: 47 Column: 46

  • Try to play your database on this site so we can try to help you: http:/sqlfiddle.com/

  • Try it this way: select d.department_id, d.department_name, max(jh.start_date), min(jh.start_date) from departments d
inner join job_history jh on jh.department_id = d.department_id
group by d.department_id order by d.department_id, d.department_name;

  • My BD is one that oracle provides for download and testing, the Diagram of it is available in image

  • @R.Santos gave the same friend error...

Show 3 more comments

2 answers

9


When group by is used, you can only set the columns to select by group by or aggregator functions (e.g., max, min, sum).

Does it make sense, otherwise, what the DBMS should return to departments that have the same Id, but different names (assuming the id is not the key)? In this case there would be more than one value to return, but each line should return only one value for the name.

If you grouped by one column, the others may have different values, and it would make no sense to return them.

So in your case you would have to group by code (id) and name.

3

Try this: 

SELECT d.department_id, d.department_name, MAX(jh.start_date), MIN(jh.start_date) 
FROM departments d
INNER JOIN job_history jh ON jh.department_id = d.department_id
GROUP BY d.department_id, d.department_name ORDER BY d.department_name;

Browser other questions tagged mysql sql database oracle

You are not signed in. Login or sign up in order to post.