SQL query, subquery counting?

Question

I have the following SQL tables

The Department

|name|employees| 

Employee

 |name|gender|type|dead | |John|male |good|yes | |Mary|female|bad |no | |Joe |male |ugly|maybe| 

I would like to write an instruction that returns

 | type | n of employees | n of male employees | n of departments | 

I have

 SELECT e.type, count(e), count(d) FROM Department d JOIN d.employees e WHERE e.dead = maybe GROUP BY e.type 

This, of course, is the absence of "male workers." I am stuck here, as I’m not sure where to specify the additional sentence e.gender = male.

I forgot to mention: HQL or criteria would be good.

Answer 1

For reference only:

 SELECT e.type, count(e), count(d), sum(case when gender = 'male' then 1 else 0 end) from Department d JOIN d.employees e WHERE e.dead = 'maybe' GROUP BY e.type 

works in HQL. Thanks everyone!

Answer 2

Assuming your original query and schema is correct:

 SELECT e.type, count(e), count(d), count (case when gender = 'male' then 1 else NULL end) AS NumberOfMaleEmployees from Department d JOIN d.employees e WHERE e.dead = 'maybe' GROUP BY e.type