select m.DEPT_NAME , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'male') , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'female')
from  my_dept m

select m.DEPT_NAME , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'male') male, 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'female') female
from  my_dept m

select d.DEPT_NAME,
    (select count(*) from my_emp where DEPT_ID =d.DEPT_ID and GENDER ='male') as male,
    (select count(*) from my_emp where DEPT_ID =d.DEPT_ID and GENDER ='female') as female,
from my_dept d

select d.DEPT_NAME,
    t.male,
    t.female
from my_dept d,(
  select DEPT_ID,sum(case when GENDER ='male' then 1 else 0 end) as male,
       sum(case when GENDER ='female' then 1 else 0 end) as female
  from my_emp
  group by DEPT_ID
  ) as t
where d.DEPT_ID=t.DEPT_ID

select d.DEPT_NAME,
    sum(case when GENDER ='male' then 1 end) as male,
    sum(case when GENDER ='female' then 1 end) as female
from my_dept d
left join my_emp e
on e.DEPT_ID =d.DEPT_ID
group by d.DEPT_NAME

select m.DEPT_NAME , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'male') , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'female')
from  my_dept m

create table t1 (userid int,username varchar(8),genger varchar(8),dept_id int)
insert into t1 select 1,'adam','male',1
     union all  select 2,'hellen','female',2
     union all  select 3,'larry','male',1
     union all  select 4,'kim','male',1
     union all  select 5,'bash','male',2
     union all  select 6,'tom','female',1
--select * from @t1
create table t2 (dept_id int,dept_name varchar(10))
insert into t2 select 1,'it'
     union all  select 2,'sales'
;with cte as
(
select b.dept_name,a.genger,count(*) 次数 from t1 a join t2 b on a.dept_id=b.dept_id
   group by dept_name,genger
)
select dept_name,male= sum(case when genger='male' then 次数 else ''end),
                 female=sum(case when genger='female' then 次数 else '' end)
        from cte
        group by dept_name
---------------------
drop table t1
drop table t2
it    3    1
sales    1    1

if object_id('t1')is not null
   drop table t1
if object_id('t2')is not null
   drop table t2
create table t1 (userid int,username varchar(8),genger varchar(8),dept_id int)
insert into t1 select 1,'adam','male',1
     union all  select 2,'hellen','female',2
     union all  select 3,'larry','male',1
     union all  select 4,'kim','male',1
     union all  select 5,'bash','male',2
     union all  select 6,'tom','female',1
--select * from @t1
create table t2 (dept_id int,dept_name varchar(10))
insert into t2 select 1,'it'
     union all  select 2,'sales'
;with cte as
(
select b.dept_name,a.genger,count(*) 次数 from t1 a join t2 b on a.dept_id=b.dept_id
   group by dept_name,genger
)
select * from (select * from cte) as a pivot (sum(次数) for a.genger in (male,female)) as c

create  table table_a(USERID int,USERNAME varchar(6),GENDER varchar(6),DEPT_ID int)
insert into table_a
select 1,'adam','male',1 union all
select 2,'hellen','female',2 union all
select 3,'larry','male',1 union all
select 4,'kim','male',1 union all
select 5,'bash','male',2 union all
select 6,'tom','female',1
select * from table_a

create table table_b(DEPT_ID int,DEPT_NAME varchar(5))
insert into table_b
select 1,'it' union all
select 2,'sales'
select * from table_b

select bb.DEPT_NAME,
    (select count(*) from table_a where DEPT_ID =bb.DEPT_ID and GENDER ='male') as male,
    (select count(*) from table_a where DEPT_ID =bb.DEPT_ID and GENDER ='female') as female
from table_b bb

/*
DEPT_NAME male        female
--------- ----------- -----------
it        3           1
sales     1           1

(2 row(s) affected)
*/

select m.DEPT_NAME , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'male') male, 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'female') female
from  my_dept m