• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

query ...

There are 2 tables - Dept(DeptId,DeptName) and Emp(EmpId,EmpName,DeptId).
What will be the query for retrieving all DeptNames and the no of employees in that dept ?
0
Amita
Asked:
Amita
1 Solution
 
ubascheCommented:

For Oracle you can use this query:

select d.deptname, count(*) as num_of_emps
from d.dept, e.emp
where d.deptid = e.deptid
group by d.deptname;

or in case some departments don't have employees (?)

select d.deptname, count(*) as num_of_emps
from d.dept, e.emp
where d.deptid = e.deptid (+)
group by d.deptname;
0
 
lauszCommented:
Try this

select deptname , count(empid )
from dept d, emp e
where d.deptid = e.deptid
group by  deptname

0
 
SNilssonCommented:

Select Dept.DeptName, count(Emp.EmpId)
From Dept, Emp
Where
Emp.DeptId = Dept.DeptId
Group By
Dept.DeptName
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
allanau20Commented:
Ubasche,

All of the above should work. But, just to help -- there's more than one way to skin sql, I mean a cat.

Try this:

SELECT     d.DeptName, Count (d.EmpId) as TotEmployeeInDepartment
FROM       Dept d
                INNER JOIN Emp e ON d.DeptId = e.DeptId
GROUP BY d.DeptName
0
 
YZlatCommented:
select Dept.DeptName, Count(Emp.EmpID) as NumberOfEmployees
FROM Dept INNER JOIN Emp on Dept.DeptID = Emp.DeptID
ORDER BY Dept.DeptName
0
 
David LittleCommented:
In order to make sure you get ALL departments, even if they don't have employees, change the join to a LEFT OUTER...

select Dept.DeptName,
         Count(Emp.EmpID) as NumberOfEmployees
FROM Dept LEFT OUTER JOIN Emp on Dept.DeptID = Emp.DeptID
ORDER BY Dept.DeptName
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now