title: CASE statement in HQL or Criteria
body: <p>derived from <a href="https://stackoverflow.com/questions/7065916/sql-statement-subquery-count">this question</a>, is it possible to use HQL or Criteria for the following SQL statement:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Although google comes up with a few hits that state HQL supports CASE statements, Hibernate 3.6.6 fails with a</p>
  
  <blockquote>
    <p>QuerySyntaxException: unexpected token: CASE</p>
  </blockquote>
  
  <p>when I create the query above on an EntityManager instance.</p>
  
  <p>How much of a bad idea is it, to create another query for every e.type to determine the number of males manually, e.g. for every e.type</p>
  
  <pre><code>SELECT 
     count(e), 
  from Department d 
  JOIN d.employees e
  WHERE e.dead = 'maybe', e.type = ugly
  </code></pre>
  
  <p>Since there could be quite a few types, this is potentially slow. I'd like the database to do the work for me.</p>