CODEWITHSUNDEEP
sqlsql-server
Sayanto Roy
Sayanto Roy

Reputation: 129

Get the count of columns with respect to the value stored in it using SQL query

I have a table where there are employee details. In the table there is column named Active. If that is 1 then the employee is active or else not. I have to show a column showing the count of Active employees and Inactive employees.

How can I do this? I have not done anything because I know that I cannot use directly where clause in here. It is in SQL Server.

Table is like:


Sno   Name   Active
____________________
1.   John      1
2.    Yuri     0
3.    Cody     1
4.    Ravi     1
5.    Tom      1
6.    Jessy    0


The resultant table :
Active   Inactive
___________________
4           2

I have to further group it by departments and maybe centers but I am stuck.

Thanks for help in advance. I would also love if you share any resource regarding this.

Upvotes: 0

Views: 307

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can use CASE like this way :

select sum(case active when 1 then 1 else 0 end) as Active, 
sum(case when active<>1 then 1 else 0 end) as Inactive
from Table1

SQL Here

Upvotes: 1

DataGuy
DataGuy

Reputation: 198

This will give you the aggregated data.

declare @active int, @inactive int


select @active = count(active)
from TableName
where active = 1


select @inactive = count(active)
from TableName
where active = 0

select @active as [Active], @inactive as [InActive]

You can also add the following code to insert into a temp table (directly after the code above):

into #tmpTable

select * from #tmpTable

That way you can join the temp table to other tables if needed (of course you would have to add a key to the temp table to do this).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Just use aggregation:

select sum(active) as numactive, sum(1 - active) as numinactive
from t;

Upvotes: 1

Related Questions