GROUP BY SQL error

Navigation
3

I have the following exercise:

  
  • List department code and name, the earliest and oldest admission date of each   department, sort by department name.
    •   

    I was able to do the following: 

    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 the error below, would 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

        
    asked by anonymous 30.08.2017 / 20:15

    2 answers

    9

    When group by is used, you can only select the columns specified in group by or aggregator functions (eg max, min, sum) in the select.

    It makes sense, therefore, what should the DBMS return to departments that have the same ID, but different names (assuming the id was 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 have grouped by one column, the others may have distinct values, and it would not make sense to return them.

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

        
    30.08.2017 / 20:24
    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;
        
    31.08.2017 / 17:43
    Image Modal only works with the first creating a voting bot in python