title: Group and count query in HQL
body: <p>I have HQL query that is giving me a headache and am hoping a friendly HQL wizard is out there who can help.</p>
  
  <p>I think I'm trying to do something really simple but can't fathom it all. </p>
  
  <p>I want to group and count some data to present it in a table and think I need a nested query of some sort but can't figure out the way to do it.</p>
  
  <p>Basically I have a table (clients) which has relations to other tables that are used as lookups (clienttype) and (clientsex). The relationships is 1-m.  I want to group the clients by type and then have a column counting males, and another counting females. e.g</p>
  
  <pre><code>Type    | males | females
  
  type A  | x     | x
  
  type B  | x     | x
  </code></pre>
  
  <p>I can do the query just to get males or females but can't figure out how to get a second column.  </p>
  
  <p>Hope this makes sense, is possible and that someone is able to help.</p>
  
  <p>Many thanks,</p>
  
  <p>Craig</p>
  
  <hr>
  
  <p>Query to get just males is:</p>
  
  <pre><code>SELECT a.clientStatus, COUNT(c.sex)
  
  FROM
  
  Tblclientstatus AS a RIGHT OUTER JOIN a.tblclients AS b
  LEFT OUTER JOIN b.tblsex AS c
  
  WHERE c.sex = 'male'
  
  GROUP BY a.clientStatus
  </code></pre>