Find the number of employees in each department - SQL Oracle

Question

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

User · Answer

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

User · Answer

Try to do this      SQL gt  select dept count     no of emp  from employee group by dept   DEPT                  no of emp -------------------- ---------- HR                            2 Account                       2 Admin                         3

User · Answer

Try the query below   select count    d dname from emp e   dept d where d deptno   e deptno group by d dname

User · Answer

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

User · Answer

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

User · Answer

Please try   select count    as count dept DNAME  from emp  inner join dept on emp DEPTNO   dept DEPTNO  group by dept DNAME

User · Answer

select count e empno   d deptno  d dname  from emp e  dep d where e DEPTNO   d DEPTNO  group by d deptno  d dname

[sql] Find the number of employees in each department - SQL Oracle

Examples related to sql

Examples related to oracle