Conditional Aggregation in sql server

Sometimes we need to get the aggregation value of the column like get the number of a male and female employee or we need to get the average salary of the male and female employees. In this case, we can use CASE expression to get the expected result.

Let’s see this example.

I have an employee table and I want to get the count of the male and female employees.

declare @emp table (emp_id int,  emp_name varchar(100),  gender char(1)) insert into @emp select 1,'Bagesh','M' union all select 2,'Kumar','M' union all select 3,'Reshama','F' union all select 4,'Naresh','M' union all select 5,'Mahesh','M' union all select 6,'Pari','F' union all select 7,'Sonnam','F'

Here I am using the CASE Expression

select sum(case when gender='M' then 1 else 0 end) as male_count, sum(case when gender='F' then 1 else 0 end) as female_count from @emp

Running this script

           

Hope this will help!