Take the 2-minute tour ×

I am using oracle's SQL Developer. To begin with, I have this table:

Name           Null     Type         
FIRST_NAME              VARCHAR2(20) 

I would like for each employee to show his name and the number of colleagues from his department. This is what I got so far:

select first_name, department_id, count(employee_id)
from employees
group by department_id;

This generates an error:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

I would really need some help. I am a total beginner so any suggestion is welcome.

UPDATE: So, for each Employee, I want to show the number of his colleagues from the same department, and his name. I have updated the question.

share|improve this question
There's nothing wrong with the syntax of your query. Are you sure there's nothing else in the buffer that you're accidentally executing along with it? Also, I'm not sure about SQL developer, but try without the terminating ;. –  Joachim Isaksson May 30 '13 at 5:50
I've updated the questionn. –  Theo. May 30 '13 at 5:52
Are you still getting the syntax error, or is the question about the new functionality now? –  Joachim Isaksson May 30 '13 at 5:53
It's the same, I need the number of colleagues so that would be the count -1 –  Theo. May 30 '13 at 5:56

3 Answers 3

up vote 3 down vote accepted

The query you posted would not generate the error you indicate

SQL> create table employees(
  2    employee_id number primary key,
  3    first_name varchar2(20),
  4    last_name varchar2(25),
  5    department_id number
  6  );

Table created.

SQL> select first_name, department_id, count(employee_id)
  2  from employees
  3  group by first_name, department_id;

no rows selected

However, it would also not produce the results that you seem to indicate that you want. From your description, it appears that you want something like

select first_name, 
       count(*) over (partition by department_id) - 1 num_colleagues_in_department
  from employees
share|improve this answer
Yes, but I would need the number of his colleagues so that would be -1, where should I insert -1 ? –  Theo. May 30 '13 at 5:55
@Theo - I'm not sure I understand what -1 represents. How can a department have -1 employees? If you are saying that you don't want to count the employee and you only want to count the other employees in the department count(*) over (partition by department_id) - 1 would be valid –  Justin Cave May 30 '13 at 5:57
SO a department has x number of employees. But the employee Y has x-1 number of colleagues, right? We have to exclude him from the count, because I need the number of colleagues for each employee. –  Theo. May 30 '13 at 5:58
@Theo - Updated my answer –  Justin Cave May 30 '13 at 6:03
Regardless of if you subtract one or not, you're way better off compared to where you started. over..partition is new to me it indeed simplifies the query! –  mikey May 30 '13 at 6:07
select a.firstname, a.department_id, 
(select count(employee_id) from employees b where b.department_id = a.department_id and b.employee_id <> a.employee_id) as total_in_dept
from employees a

this is roughly how I'd do it. It is a subquery. http://www.techonthenet.com/oracle/subqueries.php

share|improve this answer

You can do it, among other ways, using a subquery (as many other answers) or a LEFT JOIN like this one;

SELECT u.employee_id, u.first_name, u.last_name, u.department_id,
       COUNT(c.employee_id)-1 colleagues
FROM employees u
LEFT JOIN employees c
  ON u.department_id=c.department_id
GROUP BY u.employee_id, u.first_name, u.last_name, u.department_id
ORDER BY employee_id

A normal JOIN or INNER JOIN would only return users that actually have at least one colleague, a LEFT JOIN returns users even if they don't have a colleague so we can count them.

An SQLfiddle to test with.

share|improve this answer

Your Answer


