SQL Group function by function (column) - now cannot select this column

Question

I used the HR staffing scheme in Oracle Express, and I wanted to select the employees who were hired in that particular year.

SELECT hire_date, COUNT(*) FROM employees empl GROUP BY SUBSTR(hire_date, -4) ORDER BY empl.hire_date; 

The rent_date column has this format "1/1/2011", so I would like to group them by retrieving the last four char.

The problem is that I encountered the error below

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

Is it impossible?

Answer 1

You cannot select the full hire_date if you group only the last four digits. Think about what happens if you have two lines:

 hire_date ========= 01/01/2001 02/02/2001 

On one line generated during grouping, what should hire_date be hire_date ?

Each selected column must be either a column or an aggregate column. In other words, try:

 select substr(hire_date,-4), count(*) from employees group by substr(hire_date,-4) order by empl.hire_date; 

---

I should mention that some string functions are notoriously poor at scaling. If you want to process a year a lot, you should consider dividing it into your column. This can significantly improve performance, but measure, don't guess!

And, as others have already mentioned in the comments, substr is probably not the best solution, as it may depend on the language (as in: it is possible that the date will be formatted as YYYY-MM-DD , which will not work well with substring ).

It is better to use something like to_char(hire_date,'YYYY') or extract (year from hire_date) , which should be more reliable.

Answer 2

you can also crop the hiredate column

 select trunc(hiredate, 'yyyy'), count(*) from employee group by trunc(hiredate, 'yyyy') 

Answer 3

if you want employees of the group to be hired by the year

 select to_char(hiredate,'yyyy'),count(*) from employee group by to_char(hiredate,'yyyy') 

Answer 4

You can use GROUP BY clauses or aggregate functions ( MIN, MAX, AVG , etc.) in the SELECT part of the GROUP BY query. It works:

 select substr(hire_date,-4), count(*) from employees empl group by substr(hire_date,-4) order by substr(hire_date,-4);