HQL combining aggregate functions

问题:

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

回答1:

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;