Writing a Query ??

[pczafer]

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?

[debasisdas]

can any body help with this?

Sure

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

[pczafer]

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

[debasisdas]

you did not answer 2nd part of my question.

what is the query that you are working on ?

[pczafer]

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.

[debasisdas]

That is not a SQL.

You need to show effort to get any help here.

[pczafer]

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;

[debasisdas]

what is staff_number in your query ?

[pczafer]

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;

[debasisdas]

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

[pczafer]

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..

[debasisdas]

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)

[pczafer]

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

[debasisdas]

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

I have already tested that.

[debasisdas]

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)

[pczafer]

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???

[debasisdas]

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)

[pczafer]

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

[debasisdas]

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)

[pratik_garg]

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