SQL Compare employees by gender in different departments

I need help to write a query that compares the number of male managers and the number of female managers in each department.

tables are as follows.

-- Departments
---------------------------
| dept_id |  dept_name    |
+---------+---------------+
|   1     |  Marketing    |
|   2     |  Finance      |
|   3     |  Development  |
+---------+---------------+

-- Employees
----------------------------------------
|emp_no | name | gender | hired_date |
+-------+------+----------+------------+
|  1    | John |  M     | 2017-09-15 |
|  2    | Sara |  F     | 2018-02-01 |
|  3    | Eli  |  F     | 2019-01-05 |
|  4    | Alex |  M     | 2019-01-05 |
---------------------------------------------

-- dept_manager
---------------------
|emp_no | dept_id   |
+-------+-----------+
|  1    |    3      | 
|  3    |    1      | 
|  4    |    2      | 
|  4    |    3      | 
---------------------

Expected Results

-----------------------------------------------------
| dept_id    |  no_female_managers | no_male_managers |
+------------+---------------------+------------------+
|   1        |          1          |        0         |
|   2        |          0          |        1         |
|   3        |          1          |        1         |
+-----------------------------------------------------+

Any Ideas How I write this in SQL

Answer

with cte as (select emp_no,gender,dept_id
from
Employees e
join dept_manager d 
on e.emp_no=d.emp_no)

select
dept_id,
count(case when gender='F' then 1 end) as no_female_managers,
count(case when gender='M' then 1 end) as no_male_managers
from cte
group by dept_id;

Advertisement

Answer