Add result from two count queries using Hibernate HQL 5

Question

Please tell me how to request a sum of two counts in HQL so Hibernate can parse and execute. Mysql accepts the following but I would like to avoid native SQL:

select ( (select count(id) from c_cat_map) + (select count(id) from c_acc_map) ) as c;

But the HQL parser from Hibernate 5.3.9 rejects this with:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected end of subtree 

This is the same question as this 2012 post but hibernate has changed a lot since then so forgive the near duplicate: How to select the sum of multiple count() selections in JPQL

Basically same as How to write a query in hibernate for count(*) and + (addition) but there is no HQL answer.

I am just trying to avoid two round-trips to the DB, would like to get the sum in one trip. Maybe it's impossible without a native query? Thanks in advance.

Answer 1

Like many SQL dialects, HQL has a mandatory FROM clause, but it offers no dummy or DUAL table out of the box. But in your case, you can work around this limitation by running the following query:

select count(id) + (select count(id) from c_acc_map) as c
from c_cat_map