Hi all,


I was trying to write a query to List all manager's names and how many employees they are managing.
all the employees and managers in the same employees table and have a id number and and manager id number.

can any body help with this?

can any body help with this?

Sure

What is the table structure and what is the query that you are working on ?

Sure

What is the table structure and what is the query that you are working on ?

CREATE TABLE  "EMPLOYEES" 
   (	"EMPLOYEE_ID" NUMBER(6,0), 
	"FIRST_NAME" VARCHAR2(20), 
	"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, 
	"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, 
	"PHONE_NUMBER" VARCHAR2(20), 
	"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, 
	"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, 
	"SALARY" NUMBER(8,2), 
	"COMMISSION_PCT" NUMBER(2,2), 
	"MANAGER_ID" NUMBER(6,0), 
	"DEPARTMENT_ID" NUMBER(4,0), 
	 CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE, 
	 CONSTRAINT "EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE, 
	 CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL") ENABLE

/
ALTER TABLE  "EMPLOYEES" ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
	  REFERENCES  "EMPLOYEES" ("EMPLOYEE_ID") ENABLE

List the names of employees who manage 10 or more other employee, order by last name.


thanks

you did not answer 2nd part of my question.

what is the query that you are working on ?

you did not answer 2nd part of my question.

what is the query that you are working on ?

just the single query i was trying to get

SELECT the names of employees who manage 10 or more other employee, order by last name.

That is not a SQL.

You need to show effort to get any help here.

That is not a SQL.

You need to show effort to get any help here.

SELECT first_name, last_name
FROM employees
WHERE (count(staff_number) > 9)
ORDER BY last_name;

what is staff_number in your query ?

what is staff_number in your query ?

sorry my mistake

SELECT first_name, last_name
FROM employees
WHERE (SELECT manager_id,count(employee_id) > 9
GROUP BY manager_id)
ORDER BY last_name;

that will not work , you need to sub query to fetch the data.

that will not work , you need to sub query to fetch the data.

that's what was i trying to do but couldn't managed..

try this

select employee_id, last_name
  from employees
 where employee_id = (select manager_id
                        from (select manager_id, count(employee_id)
                                from employees
                               where manager_id is not null
                               group by manager_id
                               order by count(employee_id) desc)
                       where rownum < 9)

try this

select employee_id, last_name
  from employees
 where employee_id = (select manager_id
                        from (select manager_id, count(employee_id)
                                from employees
                               where manager_id is not null
                               group by manager_id
                               order by count(employee_id) desc)
                       where rownum < 9)
ORA-01427: single-row subquery returns more than one row

Make proper data entry into the table, query will work.

I have already tested that.

else use this

select employee_id, last_name
  from employees
 where employee_id IN (select manager_id
                        from (select manager_id, count(employee_id)
                                from employees
                               where manager_id is not null
                               group by manager_id
                               order by count(employee_id) desc)
                       where rownum < 9)

else use this

select employee_id, last_name
  from employees
 where employee_id IN (select manager_id
                        from (select manager_id, count(employee_id)
                                from employees
                               where manager_id is not null
                               group by manager_id
                               order by count(employee_id) desc)
                       where rownum < 9)

I have only 3 manager in my database!

this query returns 8???

this will work if only one manager has max number of employees reporting to him

SELECT employee_id, last_name
    FROM employees
    WHERE employee_id = (SELECT manager_id
    FROM (SELECT manager_id, COUNT(employee_id)
    FROM employees
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
    ORDER BY COUNT(employee_id) DESC)
    WHERE ROWNUM < 9)

this will work if only one manager has max number of employees reporting to him

SELECT employee_id, last_name

    FROM employees
    WHERE employee_id = (SELECT manager_id
    FROM (SELECT manager_id, COUNT(employee_id)
    FROM employees
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
    ORDER BY COUNT(employee_id) DESC)
    WHERE ROWNUM < 9)[/code][/QUOTE] 

thats the problem i am trying to get all the managers not only one!

ORA-01427: single-row subquery returns more than one row

what about this

SELECT distinct employee_id, last_name
      FROM employees
      WHERE employee_id IN (SELECT manager_id
      FROM (SELECT manager_id, COUNT(employee_id)
      FROM employees
      WHERE manager_id IS NOT NULL
      GROUP BY manager_id
      ORDER BY COUNT(employee_id) DESC)
      WHERE ROWNUM < 9)

Hi pczafer,

i think you have to again think about your wuery what you have posted last time.

SELECT first_name, last_name
FROM employees
WHERE EMPLOYEE_ID in 
 (SELECT manager_id from (
           select manager_id,count(*) cnt from employees 
           group by manager_id)
   where cnt >9 -- this part you can modify as you want 
)
ORDER BY last_name;

look .. easy na??
just think properly..
as you also have good knowledge of sql..it seems