Hi,
We have a scenario where we need to find sum of all males in a company.
Given the below SQL, how do we write the same in HQL.

select e.name, sum(case e.gender = 'M' then 1 else 0 end), sum(case when e.address is null then 1 else 0 end) from employee e group by ename;

This works fine in sql. I m told we can use criteria queries to complete this.
In our project, we have been using native queries and that is norm, hence we are trying to find a solution using native queries.

Thanks in advance.
Sam

hi berly_varghese,

Instead of using sum, try to use count() method to achieve, Let Employee be ur entity.

select count(e.identifier) from Employee e where e.gender='M' and e.address is null;

Get me back if this is sensible.

Thanks for the reply shynate.
I have 3 individual rows. If i use this method, I get the same count on all the rows. and that is not the expected result. Probably changing the scenario might make more sense. Find the total number of male employees in a particular department. According to the statement provided in the reply, all the counts will be the same for all rows.
Is there a way to duplicate sum(case e.gender = 'M' then 1 else 0 end) in hql.
I would really appreciate if this can be duplicated into a native query.
Thanks again.
Berly

Hi Berly,

Please make use of this HQl:

HQL : Trying to fetch count in each genders for their name title.
-------------------------------------------------------------
select count(p.title.identifier),p.title.identifier,p.gender.identifier
from person.PersonSummary p
group by p.title.identifier, p.gender.identifier


SQL : Query fired after execution of above HQL query
-------------------------------------------------------------
select
count(personsumm0_.ctitle_identifier) as col_0_0_,
personsumm0_.ctitle_identifier as col_1_0_,
personsumm0_.CGENDE_IDENTIFIER as col_2_0_
from
persons personsumm0_
group by
personsumm0_.ctitle_identifier ,
personsumm0_.CGENDE_IDENTIFIER


hope this helps u :)

Thanks Shynate again.
unfortunately this does now either.
it does not give me the required results in the real scenario.
i had added the group-bys' as needed but this resulted in all the rows to contain the same count.
The sum(case) is just a small part of a big query, and could not find a way to convert sum(case then else end) statement in SQL to HQL.
Thanks,
Berly

Hi Shynate,
That worked eventually. Thanks for your help.
I still wonder why there is no hql support for the same sum(case when then else end) statement.
I know that SQL supports it.

In this case, I am little concerned about performance when I use inner select statements.
Do you how much it might affect the query.

Thanks,
Berly

Hi Berly,

Welcome. Could you post your query which got the result , so that it can be help full for analysis.

Hi Shynate,
The exact query is confidential to the company, I apologize that I would not be able to post the query on the forum.
Thanks for helping out though.
Berly