avg() function excluding zeroes

poroto20 · **Joined:** Tue Feb 03, 2009 2:49 pm **Posts:** 4

Is there any possibility of using the avg() function excluding zeroes?

I know there is a funciton excludeZeroes() but using Criteria example.

I have this hql:

Code:

"select competitorAnalyse.competitor.id,competitorAnalyse.competitor.name,avg(qualiScore.value),avg(quantiScore.value) from " + Opportunity.class.getName() + " as opp inner join opp.competitorAnalyses competitorAnalyse inner join competitorAnalyse.qualitativeScores qualiScore inner join competitorAnalyse.quantitativeScores quantiScore where opp.id = :id group by competitorAnalyse.competitor.id,competitorAnalyse.competitor.name "

and I would like to ignore the qualiScore.value and quantiScore.value values of 0

Any ideas? Pleaase help me

nordborg · **Posted:** Thu Mar 04, 2010 4:39 pm

The avg() function will only ignore null values so the solution is to convert 0 to null. This can be done with the CASE statement:

Code:

avg(case qualiScore.value
when 0 then null
else qualiScore.value
end case)

And similar for quantiScore.value.