title: Hibernate HQL - Use CASE WHEN in COUNT Statement like IF in MySQL
body: <p>I am trying to port a MySQL Query that works to Hibernate HQL, this is all very new to me, so I am open to any kind of hint (Wrong Way, Wrong Structure, change all... ;) )</p>
  
  <p>Two tables A and B. (Structure broken down, only the relevant parts)</p>
  
  <p>A contains entrys, each with a unique ID.
  B references those IDs and holds a boolean-like marker (TINYINT(1)).</p>
  
  <p>I want to know how many rows there are in B for each row in A with the Id from A's Row and Marker == True (1).</p>
  
  <p>My MySQL query was like this:</p>
  
  <pre><code>SELECT A.id, COUNT( IF( B.marker = 1, 1, NULL ) ) AS markerTrue, COUNT( IF( B.marker =0, 1, NULL ) ) AS markerFalse FROM A LEFT JOIN B ON B.a_id = A.id GROUP BY A.id
  </code></pre>
  
  <p>It works and I ported it to this (HQL):</p>
  
  <pre><code>SELECT A.id, COUNT(CASE WHEN B.marker = 1 THEN 1 ELSE NULL END) as markerTrue, COUNT(CASE WHEN B.marker = 0 THEN 1 ELSE NULL END) as markerFalse FROM A LEFT JOIN B WITH B.a_id = A.id GROUP BY A.id
  </code></pre>
  
  <p>This throws an Exception:</p>
  
  <blockquote>
    <p>org.hibernate.hql.ast.QuerySyntaxException: unexpected token: CASE near ...</p>
  </blockquote>
  
  <p>In the logs, there is also</p>
  
  <blockquote>
    <p>org.hibernate.hql.ast.ErrorCounter - line 1:19: unexpected token: CASE
    antlr.NoViableAltException: unexpected token: CASE</p>
  </blockquote>
  
  <p>But thats just the same internal Error.</p>
  
  <p>Is there a way to do this in HQL? Is there another <em>better</em> way, like restructuring the tables, what is an experts opinion on this?</p>