Take the 2-minute tour ×

My problem is that I cannot use those two aggregate functions together like in the query: AVG(MIN(, First I need to obtain the minimum value from that entity termResultCurrent and after that the average of all the minimum values. Probably it must be changed the group by and include some other clause to fetch just the minimum value (I don't know if that is possible). Otherwise, it might be using subqueries but I had problems with the group by... I know it is quite challenging but I do appreciate your help.

SELECT  AVG(MIN(termResultCurrent.position)), count(*)
FROM TermSubscription subscription,
     Competitor competitor,     
     TermQuery termQueryPrev, IN(termQueryPrev.results) termResultPrev,     
     TermQuery termQueryCurrent,IN(termQueryCurrent.results) termResultCurrent     
 WHERE subscription.account.id= 274
      and competitor.id = 379 
      AND termQueryPrev.term=subscription.term      
      AND termQueryCurrent.term=subscription.term       
      AND termQueryCurrent.provider.id= 1  
      AND termQueryCurrent.provider.id=termQueryPrev.provider.id       
      AND termQueryPrev.queryDate.yearWeek = YEARWEEK(FROM_DAYS(TO_DAYS('2013-01-01') - 7), 1)       
      AND termQueryCurrent.queryDate.yearWeek = YEARWEEK('2013-01-01',1)      
      AND termResultPrev.url.hostname   MEMBER competitor.domains       
      AND termResultCurrent.url.hostname   MEMBER competitor.domains  
 group by  subscription.term.id
share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Use this:

 select avg(s.minimum) from
    (SELECT MIN(termResultCurrent.position) minimum
        FROM TermSubscription subscription, ...
        WHERE subscription.account.id= 274 and ...  
        group by  subscription.term.id
    ) s;
share|improve this answer
I tryed it and I got the following exception: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 2, column 5 [ select avg(s.minimum) from (SELECT MIN(termResultCurrent.position) minimun FROM TermSubscription subscription,... group by subscription.term.id) s;] I think that I saw that solution before but I wasnt able to implement because this exception. –  user1847584 Jan 30 '13 at 11:28
This works in native SQL. You can use createSqlQuery() for this. Hibernate itself might not support these subselects. When using HQL you only do the inner select [SELECT MIN(...) minimum FROM ... WHERE ... group by ...] and you calculate the average manually in the Java code. That might even be faster than doing all in the database. –  Johanna Jan 30 '13 at 11:36
Thanks Johana. I really appreciate your hekp!! –  user1847584 Jan 30 '13 at 11:37
add comment

Your Answer


