?
Solved

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

Posted on 2009-04-29
16
Medium Priority
?
2,974 Views
Last Modified: 2012-06-27
How can I modify an HQL Query to sum the result of a subquery? For example, The query below gives me a list of average ratings, but what I would like to do is get the sum of that list, and every attempt has been met with org.hibernate.hql.ast.QuerySyntaxException: unexpected token
// working inner query
select avg(vote.rating) from Vote vote
 
// failed attempts at summing
select sum(averages) from (select avg(vote.rating) as averages from Vote vote)
select sum(avg(vote.rating)) from Vote vote

Open in new window

0
Comment
Question by:justsomeguy1
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 5

Expert Comment

by:louislietaer
ID: 24263991
Are you sure you want the sum of averages, if not

select sum(vote.rating) from Vote Vote
0
 
LVL 5

Expert Comment

by:louislietaer
ID: 24264024
Anyway "select avg(vote.rating) from Vote vote" will return 1 row, and sum of 1 row has no sense !
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 24264029
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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:justsomeguy1
ID: 24264030
Yes I'm not looking for the sum of all ratings, I'm trying to get the sum of the averages of all ratings
0
 

Author Comment

by:justsomeguy1
ID: 24264073
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
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 24264077
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.
0
 
LVL 5

Expert Comment

by:louislietaer
ID: 24264097
Explain what is a rating (in the context)

0
 

Author Comment

by:justsomeguy1
ID: 24264136
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
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 24264141
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.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 24264183
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

Open in new window

0
 
LVL 5

Expert Comment

by:louislietaer
ID: 24264195
Can you please send us the definition of the table Vote ?
0
 

Author Comment

by:justsomeguy1
ID: 24264331
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.
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 24264471
The other thing you can try is to use Hibernate to get the initial resultset with average value per story, then in java code sum the values as you read through the results.
0
 

Author Comment

by:justsomeguy1
ID: 24264486
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.
0
 
LVL 1

Assisted Solution

by:cmaslen
cmaslen earned 1000 total points
ID: 24269086
HQL doesn't support the equivalent of a nested table expression that is available in most SQL databases. You'll have to execute a SQL statement via the createSQLQuery method from the hibernate Session object. If you stick with SQL-92 SQL syntax you should be fine supporting multiple databases if this is why you have to use HQL.

More info at the following link:
http://docs.jboss.org/hibernate/stable/core/reference/en/html/querysql.html

Christian.
0
 

Author Closing Comment

by:justsomeguy1
ID: 31576126
Not the solution I was hoping for but always limitations in life ha, thank you both
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question