HQL Query: Sum the result of an aggregate function like avg()

Are you sure you want the sum of averages, if not

select sum(vote.rating) from Vote Vote

Anyway "select avg(vote.rating) from Vote vote" will return 1 row, and sum of 1 row has no sense !

This should return 1 value:

select avg(vote.rating) from Vote vote

Since you have no group by statement, that will average all rating values for the entire table together; therefore, wouldn't make sense to need a sum after the fact.

Don't believe you can use two aggregates together, so your derived table looks like the right approach from a SQL syntax perspective.  Just have to alias the derived table -- at least that would be the issue if running in against database directly.  May have other issues in HQL.

select sum(t.averages) from (select avg(vote.rating) as averages from Vote vote) as t

Yes I'm not looking for the sum of all ratings, I'm trying to get the sum of the averages of all ratings

Sorry I had omitted the groupBy clause to try and make it simple, but you are right without it this particular example doesn't make sense. The actual query will have the groupby clause.

I tried your alias idea in the HQL and crossed my fingers, but I am still seeing the unexpected token exception. Here is the full query I tried:

select sum(t.averages) from (
select avg(vote.rating) as averages from Vote vote, Story story where story.id = vote.objectId group by vote.objectId ) as t

But that is the point.  If you average all the ratings, you will get one number.

rating1 = 100
rating2 = 75
rating3 = 90
rating4 = 85

Result of your initial SQL query should be 87.5.

Taking a sum of that gives you 87.5.  Doesn't make sense to do that unless you are getting multiple values somehow which would mean your initial query is not as simple as shown which may contribute to the error you are getting; therefore, please post actual query.

Otherwise, as stated, I know there are issues with using derived table without giving it an alias as I showed.

Explain what is a rating (in the context)

A story may have multiple votes, a vote has a rating from 1 to 5, so in the scenario:

Story, Rating
1, 5
1, 5
2, 1
2, 3

The result I am looking for would be 9, (5 + 4). My inner query returns a List of 5 and 4 so far

That looks ok from a SQL syntax perspective aside from the join statement I would suggest using ANSI compliant join.

select sum(t.averages) as sum_of_averages
from (
select avg(vote.rating) as averages
from Vote vote
inner join Story story on story.id = vote.objectId
group by vote.objectId
) as t

Will have to take a step back and think about what the HQL error message you are getting is referring to with invalid token message.

Try with this SQL:

select sum(v.ratingAvg) as sumOfAverages
from Story s
inner join (
	select objectId, avg(rating) as ratingAvg
	from Vote
	group by objectId
) v on s.id = v.objectId

Select allOpen in new window

Can you please send us the definition of the table Vote ?

Vote has id, objectId, and rating.

Unfortunately, a lot of the suggested queries work fine in a mysql client, but I am bound to HQL in my application. Thats why I tried to make the initial question simpler, the real problem is how I can use two aggregate functions through correct HQL syntax.

This is actually what I am doing as a working around mwvisa, its just terribly innefficient because the list of results is huge and it gets hit very often.

Not the solution I was hoping for but always limitations in life ha, thank you both