Solved

query ...

Posted on 2003-11-20
6
494 Views
Last Modified: 2012-05-04
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
Comment
Question by:Amita
6 Comments
 
LVL 3

Expert Comment

by:ubasche
ID: 9787200

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
 
LVL 6

Expert Comment

by:lausz
ID: 9787201
Try this

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

0
 
LVL 8

Expert Comment

by:SNilsson
ID: 9787210

Select Dept.DeptName, count(Emp.EmpId)
From Dept, Emp
Where
Emp.DeptId = Dept.DeptId
Group By
Dept.DeptName
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 5

Expert Comment

by:allanau20
ID: 9789381
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
 
LVL 35

Expert Comment

by:YZlat
ID: 9790558
select Dept.DeptName, Count(Emp.EmpID) as NumberOfEmployees
FROM Dept INNER JOIN Emp on Dept.DeptID = Emp.DeptID
ORDER BY Dept.DeptName
0
 
LVL 2

Accepted Solution

by:
David Little earned 50 total points
ID: 9791458
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ODBC Question 3 55
compare between entities in entity framework 8 114
Mysql Left Join Case 10 101
C# DataTable (in memory) vs SQL Lookups 4 28
Read about achieving the basic levels of HRIS security in the workplace.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question