group by clause in JPQL query

Question

I've a JPQL query which returns result without aggregates

public List<Transaction> getProjTransactionsByParametersId(Long id){
        StringBuilder sb = new StringBuilder("FROM Transaction AS t WHERE t.account.id = :id and status = 0");

        Query q = em.createQuery(sb.toString(), Transaction.class)
                .setParameter("id", id);

        List<Transaction> projTransactions = q.getResultList();

        return projTransactions;
    }

How can I incorporate group by here? I want somethinf like:

public List<Transaction> getProjTransactionsByParametersId(Long id){
        StringBuilder sb = new StringBuilder("SELECT sum(t.amount), t.valueDate FROM Transaction AS t WHERE t.account.id = :id and status = 0 GROUP BY t.valueDate");

        Query q = em.createQuery(sb.toString(), Transaction.class)
                .setParameter("id", id);

        List<Transaction> projTransactions = q.getResultList();

        return projTransactions;
    }

In this case, do I have to create some new field for that sum aggregate in Transaction mapping? I've read literature from other sources, but I couldn't understand logic.

For instance for valueDate in mapping I have getter which returns its value into my table. What about this aggregate column?

Answer 1

Just give it an alias, like sum(t.amount) as amountSum. Then read read the values from the list of arrays (or use other similar alternatives):

List<Object[]> projTransactions = q.getResultList();
for (Object[] objects : projTransactions) {
   Number amountSum = (Number) objects[0]; // Use appropriate data type
   Date valueDate = (Date) objects[1];
}

Also, remove the (wrong) class argument from the query creation method:

Query q = em.createQuery(sb.toString())