CODEWITHSUNDEEP
sql
Shashi
Shashi

Reputation: 11

Count Query with joins needed

i have table as

Dept

DEPTNO  DNAME   LOC
10  ACCOUNTING  NEW YORK
20  RESEARCH    DALLAS
30  SALES   CHICAGO
40  OPERATIONS  BOSTON

and another table as

Emp

EMPNO   ENAME   JOB          MGR    HIREDATE    SAL     COMM    DEPTNO
7369    SMITH   CLERK       7902    12/17/1980  800     NULL     20
7499    ALLEN   SALESMAN    7698    2/20/1981   1600    300      30
7521    WARD    SALESMAN    7698    2/22/1981   1250    500      30
7566    JONES   MANAGER     7839    4/2/1981    2975    NULL     20
7654    MARTIN  SALESMAN    7698    9/28/1981   1250    1400     30
7698    BLAKE   MANAGER     7839    5/1/1981    2850    NULL     30
7782    CLARK   MANAGER     7839    6/9/1981    2450    NULL     10
7788    SCOTT   ANALYST     7566    12/9/1982   3000    NULL     20
7839    KING    PRESIDENT   NULL    11/17/1981  5000    NULL     10
7844    TURNER  SALESMAN    7698    9/8/1981    1500    0        30
7876    ADAMS   CLERK       7788    1/12/1983   1100    NULL     20
7900    JAMES   CLERK       7698    12/3/1981   950     NULL     30
7902    FORD    ANALYST     7566    12/3/1981   3000    NULL     20
7934    MILLER  CLERK       7782    1/23/1982   1300    NULL     10

My question : List ALL the department names and their employee count. Count should include only those employees whose hire date is greater than 1981

Result: should be like this

DNAME       EMPCOUNT
ACCOUNTING    1
OPERATIONS    0
RESEARCH      2
SALES         0

Upvotes: 0

Views: 69

Answers (4)

Mureinik
Mureinik

Reputation: 311348

SELECT    dname,
          SUM(CASE WHEN emp.hierdate > '1981/01/01' THEN 1 ELSE 0 END)
FROM      dept 
LEFT JOIN emp ON dept.deptno = emp.empno
GROUP BY  dname

Upvotes: 2

dotnetom
dotnetom

Reputation: 24901

Try this code:

SELECT Dept.DNAME, COUNT(Emp.EMPNO) 
FROM Dept LEFT JOIN Emp ON Dept.DEPTNO = Emp.DEPTNO
WHERE Emp.HIREDATE > '1981/01/01'
GROUP BY Dept.DNAME

Upvotes: 1

Barranka
Barranka

Reputation: 21047

First, understand how to join (read about inner join, left join and right join).

Once you've got that, you should be able to write a query like this:

-- First example
select dept.dname, count(emp.empId)
from dept left join emp on dept.deptNo = emp.deptNo
group by dept.dname;

or this:

-- Second example
select dept.dname, sum(case when emp.empId is not null then 1 else 0 end)
from dept left join emp on dept.deptNo = emp.deptNo
group by dept.dname;

Check SQL fiddle example

Upvotes: 0

MattyB
MattyB

Reputation: 952

It involves a bit of trickery in order to get the departments with 0 matching employees

   SELECT Dept.DNAME, COALESCE(t.cnt, 0) AS count
     FROM Dept 
LEFT JOIN (  SELECT deptno, count(*)
               FROM Emp
              WHERE HIREDATA > '1981/01/01'
           GROUP BY deptno) as t
       ON t.deptno = Dept.deptno

Upvotes: 1

Related Questions