Tuesday, May 1, 2012

Query for getting no of Male & Female for a particular Dept - Sql Server 2005

--create table.
create table Emp
(
            Dept varchar(2),
            Name varchar(50),
            Gender Varchar(1)
)

--Insert data into table.

insert into Emp
values('A','Test','M')

insert into Emp
values('A','Test','F')

insert into Emp
values('B','Test','M')

insert into Emp
values('B','Test','M')

insert into Emp
values('A','Test','M')

insert into Emp
values('B','Test','F')

insert into Emp
values('A','Test','M')

insert into Emp
values('C','Test','F')

insert into Emp
values('C','Test','M')

insert into Emp
values('A','Test','M')

insert into Emp
values('A','Test','F')

--query--

select Dept, SUM(Case when gender = 'M' then 1 else 0 End) as Male,SUM(Case when gender = 'F' then 1 else 0 End) as Female from Emp Group by Dept

--Result-----

Dept    Male    Female
A         4          2
B         2          1
C         1          1

Happy Coding...


Posted by at

1 comment:

  1. Ravi GuptaMay 7, 2012 at 11:15 AM

    This is good one, haven't thought earlier about using aggregate functions on computed columns. Plus One!!

    You can try below as well,

    SELECT dept, m Male, f Female
    FROM
    (
    SELECT e.dept, e.gender, COUNT(*) genderCount
    FROM emp e
    GROUP BY dept, gender
    ) up
    PIVOT
    (
    SUM(genderCount) FOR gender IN (m, f)
    ) AS pvt
    ORDER BY dept

    ReplyDelete

Subscribe to: Post Comments (Atom)