JPA count query to count total result rows of a group by query

hibernate, hql, jpa

Solution

Your query will return an array of Long. Each Long is the count of `UserTransaction` for a given MerchantMaster name.

if you have your query in a NamedQuery, you can use (if not, you can use the `creatyQuery()` method and use your JPQL query String):

TypedQuery<Long> query = em.createNamedQuery("UserTransaction.MyQuery", Long.class);
/* The list have the count of User Transaction grouped by Merchant name */
List<Long> userTransactionsPerMerchantName = query.getResultList();

You probably will also need the Merchant name, you can add the merchant name to your query:

SELECT COUNT(ut), ut.merchantMaster.fullName
FROM UserTransaction ut 
WHERE ut.status = :stus AND ut.transactionDate between :frm AND :toDt"
GROUP BY ut.merchantMaster.fullName

Now, the query will return an array of objects, the first object is the count, and the second is the name. You also need to modify the code to get the results:

TypedQuery<Object[]> query = em.createNamedQuery("UserTransaction.MyQuery", Object[].class);
List<Object []> results = query.getResultList();
for (Object [] row : results) {
    Long count = (Long) row[0];
    String name = (String) row[1];
}

Problem

How to get total result rows below SQL query in JPQL: ``` select count(usertransa0_.id) as col_0_0_ from user_transaction usertransa0_ cross join merchant_master merchantma1_ where usertransa0_.merchant_master=merchantma1_.id and usertransa0_.status=0 and ( usertransa0_.transaction_date between '2015-02-19' and '2015-02-20' ) group by merchantma1_.full_name; ``` I have tried with: ``` SELECT COUNT(ut) FROM UserTransaction ut WHERE ut.status = :stus AND ut.transactionDate between :frm AND :toDt" GROUP BY ut.merchantMaster.fullName ``` But it returns only first rows count, How can get total rows count? EDIT: My entity looks like: ``` public class UserTransaction extends BaseEntity { @OneToOne(fetch = FetchType.LAZY) private UserInfo info; @OneToOne(fetch = FetchType.LAZY) private MerchantMaster merchantMaster; @OneToOne(fetch = FetchType.LAZY) private PaymentMaster paymentMaster; @Column(name = "AMOUNT") private BigDecimal amount; @Temporal(TemporalType.TIMESTAMP) @Column(name = "TRANSACTION_DATE") private Date transactionDate; @Column(name = "STATUS") private Integer status; private String userTransactionID; @OneToOne(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY) private UserTransactionRequest trRequest; @OneToOne(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY) private UserTransactionResponse trResponse; //getters & setters } ```

Original source