[FIXED] How can I use an alias with HAVING clause?

Issue

I'm trying to JPQL equivalent to the following SQL.

SELECT last_name, COUNT(1) AS c
FROM actor
GROUP BY last_name
HAVING c > 1
ORDER BY c DESC

I tried with the following JPQL.

SELECT e.lastName, COUNT(e.lastName) AS c
FROM Actor
GROUP BY e.lastName
HAVING c > 1
ORDER BY c DESC

And Hibernate generates following SQL and complains.

    select
        actor0_.last_name as col_0_0_,
        count(actor0_.last_name) as col_1_0_ 
    from
        actor actor0_ 
    group by
        actor0_.last_name 
    having
        c>=? 
    order by
        col_1_0_ DESC limit ?

When I changed the JPQL like this, it works.

SELECT e.lastName, COUNT(e.lastName) AS c
FROM Actor
GROUP BY e.lastName
HAVING COUNT(e.lastName) > 1
ORDER BY c DESC

How can I use the alias named c with the HAVING clause?

If I just can not, is the query ok? any optimal way to do that?

Thank you.

Solution

JPQL does not support using the alias in the HAVING clause, mainly because not all SQL engines support this behavior. For example, both Oracle and Postgres would not support your first version of the query.

If you want to use pure JPQL, then just repeat the entire count expression in the HAVING clause. If you really want to reuse the alias in the HAVING clause, then make your query a native one, and it will run without any problems assuming MySQL be the underlying database.

Answered By - Tim Biegeleisen
Answer Checked By - David Marino (JavaFixing Volunteer)