CODEWITHSUNDEEP
sqlsql-server-2008
Vikram Sharma
Vikram Sharma

Reputation: 387

Group By on same column in subquery

I have a sql table called Employee in which I have EmployeeID, Status and DepartmentID columns. Now I have been assigned with the task of creating a tabular report where in I need count of total employees for a department, count of Employees which are active(Status), and DepartmentID. Getting the Count of total employees and corresponding DepartmentID is simple with a Group By clause.

Select count(*) as 'Total Employees',DepartmentID from Employees 
Group By DepartmentID

How would I get the Count of Employees with a particular Status for the same DepartmentID as the outer query? Please let me know if you want some more clarifications about the problem.

Upvotes: 0

Views: 82

Answers (2)

Mohini Mhetre
Mohini Mhetre

Reputation: 919

Try this one.

Select count(*) as 'Total Employees',
COUNT(CASE WHEN status='Active' THEN 1 ELSE 0 END ) as TotalActiveEmployees,
DepartmentID 
from Employees 
Group By DepartmentID

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

If you have column Status with values active , no need of outer query

   Select count(*) as 'Total Employees',
          SUM(CASE WHEN status='Active' THEN 1 ELSE 0 END ) as TotalActiveEmployees,
          DepartmentID 
    from Employees 
    Group By DepartmentID

Upvotes: 1

Related Questions