CODEWITHSUNDEEP
javasqlhibernatejpahql
Jewels
Jewels

Reputation: 1046

Can I add amounts from multiple tables in hql

I would like to perform a query in HQL similar to this in SQL:

SELECT (
    SELECT COUNT(*) FROM EMPLOYER +
    SELECT COUNT(*) FROM COMPANIES
       ) 
FROM DUAL

When I add "FROM DUAL" to the query I get an error:

org.hibernate.hql.ast.QuerySyntaxException: DUAL is not mapped

And if I leave off the "FROM DUAL" I get:

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

Any recommendations?

Upvotes: 0

Views: 820

Answers (2)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154200

Since you didn't join the Company and Employee tables you'd better run two queries:

int companyCount = ((Number) getSession().createQuery("select count(*) from Company").uniqueResult()).intValue();
int employeeCount = ((Number) getSession().createQuery("select count(*) from Employee").uniqueResult()).intValue();
int totalCount = companyCount + employeeCount;

The (Number) is needed since PostgreSQL returns a BigInteger while HDSQLDB returns an Integer, but both are subclasses of Number.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271211

In SQL, subqueries need their own set of parentheses, so try this:

SELECT ((SELECT COUNT(*) FROM EMPLOYER) +
        (SELECT COUNT(*) FROM COMPANIES)
       )

If that doesn't work, resort to a from clause:

SELECT e.cnt + c.cnt
FROM (SELECT COUNT(*) as cnt FROM EMPLOYER
     ) e CROSS JOIN +
     (SELECT COUNT(*) as cnt
      FROM COMPANIES
     ) c

Upvotes: 1

Related Questions