CODEWITHSUNDEEP
sqloracle-database
user3395936
user3395936

Reputation: 681

Find the number of employees in each department - SQL Oracle

I have two tables Emp and Dept and I am trying to display how many people work in each department along with their department name, but I can't get it to work. I have tried this select count(ename) as count from emp group by deptno; but the output I am getting is this :

 COUNT
----------
     6
     5
     3

But I also want to show the names for each of those departments. Here are my tables :

Empt:

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

Dept: 

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

Upvotes: 2

Views: 205532

Answers (8)

Rahul
Rahul

Reputation: 179

select count(EMPNO),DNAME from EMP e join DEPT d on e.DEPTNO = d.DEPTNO group by DNAME ;

Upvotes: 0

padma
padma

Reputation: 11

Try to do this: 

SQL> select dept,count(*) "no of emp" from employee group by dept;

DEPT                  no of emp
-------------------- ----------
HR                            2
Account                       2
Admin                         3

Upvotes: 1

S Paul
S Paul

Reputation: 1

select  d.dname
       ,count(e.empno) as count 
from dept d 
left outer join emp e 
  on e.deptno=d.deptno 
group by d.dname;

Upvotes: 0

Ray
Ray

Reputation: 71

A request to list "Number of employees in each department" or "Display how many people work in each department" is the same as "For each department, list the number of employees", this must include departments with no employees. In the sample database, Operations has 0 employees. So a LEFT OUTER JOIN should be used.

SELECT dept.name, COUNT(emp.empno) AS count
FROM dept
LEFT OUTER JOIN emp ON emp.deptno = dept.deptno
GROUP BY dept.name;

Upvotes: 7

user9526167
user9526167

Reputation: 21

Try the query below:

select count(*),d.dname from emp e , dept d where d.deptno = e.deptno
group by d.dname

Upvotes: 2

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

SELECT d.DEPTNO
    , d.dname
    , COUNT(e.ename) AS count
FROM   emp e
      INNER JOIN dept d ON e.DEPTNO = d.deptno
GROUP BY d.deptno
      , d.dname;

Upvotes: 1

Gaurav
Gaurav

Reputation: 124

select count(e.empno), d.deptno, d.dname 
from emp e, dep d
where e.DEPTNO = d.DEPTNO 
group by d.deptno, d.dname;

Upvotes: 2

Eray Balkanli
Eray Balkanli

Reputation: 7990

Please try:

select count(*) as count,dept.DNAME 
from emp 
inner join dept on emp.DEPTNO = dept.DEPTNO 
group by dept.DNAME

Upvotes: 21

Related Questions