Hello i have facing some problem while using COUNT with group by

My query is

SELECT T FROM Transaction T LEFT JOIN T.payer Pay JOIN FETCH T.entity E LEFT JOIN T.provider Pro GROUP BY Pro.id HAVING T.type = 1 AND E.id = 3

This woks fine but for pagination i also need to get the count

SELECT COUNT(*) FROM (SELECT Transaction T LEFT JOIN T.payer Pay JOIN FETCH T.entity E LEFT JOIN T.provider Pro GROUP BY Pro.id HAVING T.type = 1 AND E.id = 3)
But this doesn't can i get some help how i can achieve this using HQL. Many thanks

The first query is wrong. I guess you're using MySQL since no other DB supports GROUP BY without aggregation functions.

Are yu using GROUP BY as a substitute for DISTINCT?

that query was meant to be HQL one but correct me if i am wrong i think HQL doest support GROUP BY . Here is the link

http://docs.jboss.org/hibernate/orm/5.2 ... l-group-by

What i was trying to convert these 2 native queries to HQL

SELECT COUNT(*) FROM (SELECT * FROM transaction AS T GROUP BY T.provider_id) t

SELECT * FROM transaction AS T
LEFT JOIN payer AS P ON P.id = T.payer_id
LEFT JOIN transaction_plan AS planIds ON planIds.transaction_id = T.id
LEFT JOIN plan AS PL ON PL.id = planIds.plan_id
GROUP BY T.provider_id
ORDER BY t.transactionDate

Hibernate support GROUP BY, but your query is wrong. First of all, the SQL query only counts the rows, albeit using a derived table that's not needed here.

So if you only need to count, then the HQL should be:



Now, do you really have a provider_id property in Transaction?

Also, in your initial HQL query, the joins are useless too.

i tried the query i am getting the list of ids but infact i was expecting single count. For example below is the the result i am getting but the native count query above gives single count after applying various conditions.

[23, 47, 51, 1, 1, 1, 1, 8, 1, 1, 1, 4, 2, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 2, 2, 40]

I was using joins because i have to use apply various conditions . I have to get the count for paginations and then also need to fetch the records conditions applied to both . To give you the idea consider the following query
This give single count after applying various conditions. I will be grateful if both count and then records can be fetched using either HQL or Criteria

SELECT COUNT(*)
FROM (
SELECT * FROM transaction AS T
LEFT JOIN payer AS P ON P.id = T.payer_id
LEFT JOIN transaction_plan AS planIds ON planIds.transaction_id = T.id
LEFT JOIN plan AS PL ON PL.id = planIds.plan_id
GROUP BY T.provider_id
)

How come there is no WHERE or ON clause then? I see no filtering criteria to justify the query.

If the SQL query does not make any sense, it's improbable that you can translate it to HQL.

ok i just gave the subset of query. The final query for counting the rows and for fetching the records based on conditons is as follow. It contains all the possible conditions i require

SELECT COUNT(*)
FROM (
SELECT * FROM transaction AS T GROUP BY T.provider_id
HAVING type = 1 AND
T.entity_id = 3 AND
T.provider_id IN(1) AND
T.payer_id IN(1) AND
T.parStatus IN('Non-Par','PAR')) t

And fetching query

SELECT * FROM transaction AS T
LEFT JOIN payer AS P ON P.id = T.payer_id
LEFT JOIN transaction_plan AS planIds ON planIds.transaction_id = T.id
LEFT JOIN plan AS PL ON PL.id = planIds.plan_id
GROUP BY T.provider_id
HAVING type = 1 AND
T.entity_id = 3 AND
T.provider_id IN(1) AND
T.payer_id IN(1) AND
T.parStatus IN('Non-Par','PAR')
ORDER BY t.transactionDate

Can i convert this to HQL or using Criteria API.

The firt one cannot be translated since it uses a derived table. But, Hibernate can execute native queries too, so no need to translate it to HQL.

The second one can be translated to HQL, and it's quite trivial.

thank you for the support. I will go with native query then.