How to find sum of multiple columns in where clause

Question

In my application I have an accounting table with columns amount, paidAmount, fine. Here I need to find accounts with balance zero by adding these 3 column and matching it with zero. Below is my hql query which results an error.

query = session.createQuery("FROM Accounts where accountId = :accId and SUM(amount + paidAmount + fine) = 0 order by name");

with error as:

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1613)
    .......
    ... 106 more
    Caused by: java.sql.SQLException: Invalid use of group function
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
    ... 121 more

How can I execute this where condition using HQL query

Answer 1

You have a simple syntax error.

Your query should be:

query = session.createQuery("FROM Accounts where accountId = :accId and (amount + paidAmount + fine) = 0 order by name");

SUM() cannot be used like that.

Or maybe you are trying to do something equivalent to:

SELECT * FROM `table`  GROUP BY <some_column> HAVING SUM(column1+column2)>0 ORDER BY column_name ASC

If that is the case you can use GROUP BY and HAVING.