Amita
asked on
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 ?
What will be the query for retrieving all DeptNames and the no of employees in that dept ?
Try this
select deptname , count(empid )
from dept d, emp e
where d.deptid = e.deptid
group by deptname
select deptname , count(empid )
from dept d, emp e
where d.deptid = e.deptid
group by deptname
Select Dept.DeptName, count(Emp.EmpId)
From Dept, Emp
Where
Emp.DeptId = Dept.DeptId
Group By
Dept.DeptName
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
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
select Dept.DeptName, Count(Emp.EmpID) as NumberOfEmployees
FROM Dept INNER JOIN Emp on Dept.DeptID = Emp.DeptID
ORDER BY Dept.DeptName
FROM Dept INNER JOIN Emp on Dept.DeptID = Emp.DeptID
ORDER BY Dept.DeptName
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;