SQL conditional select -> HQL help

oluckboy@hotmail.com · **Joined:** Tue Jun 20, 2006 10:20 pm **Posts:** 6

So what i'm looking for is the best replacement for the MySQL "if" expression in a select (see the SQL below) in HQL. I have solved the problem using a subquery, but I'm looking for a more eligant/faster solution to this problem. I haven't run across anything in the manual that would suggest a better way in HQL to perform the below query.

The problem is trying to get the counts of a catagorical variable (as a varchar) inside a group by. I want the number of times foo="yes" and number of times foo="no" for each bar.

Hibernate version: 3.1
Database: MySQL 5.0.22

The SQL:

Code:

select
   sum(if(foo='yes', 1, 0)) numYes,
   sum(if(foo='no', 1, 0)) numNo
from myTable
group by myTable.bar

My Current HQL:

Code:

select 
  (select count(t2) from MyTableObject t2 where t1 = t2 and t1.foo='yes') as numYes, 
  (select count(t2) from MyTableObject t2 where t1 = t2 and t1.foo='no') as numNo
from MyTableObject t1
group by t1.bar

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You can use "case when foo = 'yes' then 1 else 0 end". Have a look at section 14.9 of the ref docs for allowable expressions.