SQL Group By function(column) - Now can't Select that column

Question

I used the HR employee schema in Oracle Express and I wanted to select employees that were hired on a particular year.

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

The hire_date column has this format "1/1/2011" so I'd like to group them by extracting the last four char.

Problem is, I am encountering below error

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

Is this not possible?

Is the hire_date column a date or a character type? The output from: 'describe employees' will tell you this. — richj, May 26 '11 at 07:45
Why are you using `substr()` on a `date` column? substr() is for varchar columns. If you need to get parts of a date, you should use `to_char()` (or extract) instead. You are relying on implicit data conversion that is bound to break if the locale settings are changed. — , May 26 '11 at 07:51
@a_horse_with_no_name Thanks. Its my first time to work with Oracle DB but I have put this in my TODO list. Appreciate your input.. =) — Mark Estrada, May 26 '11 at 08:35

paxdiablo · Accepted Answer · 2011-05-27T03:36:09.497

You can't select the full hire_date if you're grouping by only the last four digits of it. Think of what will happen if you have the two rows:

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

In the single row generated when you group those, what should the hire_date be?

Every column selected must either be a group-by 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 per-row functions are notoriously bad at scaling. If you want to process the year a lot, you should consider splitting it into its own column. That may greatly improve performance, but measure, don't guess!

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

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

And you should also mention that doing a SUBSTR on a date is a VERY bad idea (as already mentioned by a_horse_with_no_name). "group by extract(year from hire_date)" is the way to go. — Rob van Wijk, May 26 '11 at 08:25
@Rob, yes, assuming it _is_ a date column. It may be a char/varchar, which is a _different_ problem :-) — paxdiablo, May 26 '11 at 10:25

score 7 · Answer 2 · answered May 26 '11 at 09:13

7

you can also truncate the hiredate column

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

answered May 26 '11 at 09:13

schurik

7,798
2
23
29

score 1 · Answer 3 · answered May 26 '11 at 07:46

1

if you want group employees by the year they were hired in use

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

answered May 26 '11 at 07:46

josephj1989

9,509
9
48
70

score 0 · Answer 4 · answered May 26 '11 at 07:41

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

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

SQL Group By function(column) - Now can't Select that column

4 Answers4