hey there, I am working on this project where I need to retreive merchant objects and their corresponding number of failed transactions.
In pure SQL, I'd just write :
"select merchant_id , count(merchant_id) from Transactions where status = 'failed' group by merchant_id"
however, the only way I managed to write the query and have something returned for hql is:
select max(T.merchant), count(T.merchant) from Transaction T where T.status = 'failed' group by T.merchant
now, max(T.merchant) is where I am afraid I could do better. but without it, I get a "not a group by statement" error. Although my query seems to work well, I am wondering if I can do this better. MAX(T.merchant) is not limiting the number of rows to 1, which is what I want, but contrary to my expectation for how MAX would work. Anyone?
Thanks in advance.