HQL query from given SQL query

Question

I got such SQL query:

SELECT COUNT(count) as ovcount from (SELECT COUNT(id) AS count FROM table WHERE
field1=value1 and field2=value2 GROUP BY something1, something2, something3)
as T WHERE count > 1

How would that statement look in HQL? Hibernate doesn't support "the easy way" if a query contains subquery after FROM.

Diego Pino · Accepted Answer · 2012-12-14 15:02:05Z

1

In the end what I think you're trying to do is to get only COUNT(id) if the total is higher than 1. Using HAVING should give you the same result.

SELECT COUNT(id) AS count 
FROM table 
WHERE field1=value1 and field2=value2 
GROUP BY something1, something2, something3
HAVING COUNT(id) > 1

answered Dec 14, 2012 at 15:02

Diego Pino

11.6k1 gold badge59 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Raidmaster Over a year ago

Not quite. What you suggest is simply gonna return a set of rows where count > 1. What I want is get a one value, which is the count of that set. (F.e. what you suggest is gonna return: row 1 - count 2, row 2 - 2, row 3 - 3, and I want to get 7 (2+2+3))

Diego Pino Over a year ago

OK, I see, thanks for clarifying. Then, one possible solution would be to get the list of results and sum them programmatically. If you still want to do it in one single HQL statement, I cannot think of anything at this moment. As you mentioned, HQL doesn't support subqueries in the FROM clause (only in SELECT and WHERE)

Raidmaster Over a year ago

I actally made a mistake, in the second phase I wanted to return 3 - the number of row, not 7 the sum of values. So your solution is good (I actually stumbled upon it in the meantime). I was just wondrering if it's optimal compared to returning the same result by a query alone

Collectives™ on Stack Overflow

HQL query from given SQL query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related