group « Query « JPA Q&A

1. What adavantages does HQL have over SQL for Group By queries

I am concentrating this question on 'reporting-type' queries (count, avg etc. i.e. ones that don't return the domain model itself) and I was just wondering if there is any inherent performance ...

2. HQL Query using group by

I have the following class structure FlowerDAO with the fields (mapped using Hibernate):

  • id
  • unitPrice
  • name
  • color How can i create an hql query to obtain the flower(s) for each color that has(ve) the minimum unit ...

3. Add a Group by to a Hibernate Criteria-Query without Projection

I have a Criteria-Query, which joins a second table B, to select entities from table A. The problem is, that this query returns some entities from table A multiple times. But ...

4. Group by month with criteria in Hibernate

I'm trying to get a report using Criteria and ProjectionList, and I'm pretty new using this through hibernate. So I have this model:

private Long _userId;

 private Category _category;

 private Long _companyId;


5. JPQL / SQL: How to select * from a table with group by on a single column?

I would like to select every column of a table, but want to have distinct values on a single attribute of my rows (City in the example). I don't want extra ...

6. How to group results by intervals?

I have a table containing events with a "speed" property. In order to see the statistical distribution of this property, I'd like to group the results by intervals, let's say:

[0-49.99km/h] 3 objects
[50-100km/h] ...

7. Using group by and having in a JPA query

How would you do the following using the JPA query language?

select * from person where email in
(select email from person group by email having count(email)>1)

8. Help with HQL query (group by, order by)

I have problem writing HQL to display distinct applicationId with the latest application (newest createDate) for the following data in the table.

| applicationId | firstName   | lastName | createDate ...

9. Hibernate Criteria: Projecting Count with group by clause

I want to execute the following SQL

select count(*) as myCount from user group by name;
I came up with the following criteria for the same

10. Hql join and group by problem

I am trying to make a query in hql. I have these beans: Bean1

@Table(name = "bean1")
public class Bean1 {


11. JPQL: efficient way to use GROUP BY

I need to do the equivalent of this efficiently in JPQL:

select name, max(rank) as MaxRank
    from books
    group by name
When I run this SQL ...

12. Can I use group by in a subquery?

I want to do the following:

select count(t) from Ticket t where in (select from Ticket st group by st.owner)
Unfortunately, I get a SQLGrammarException when doing this. Any ideas?

13. Hibernate - cant use GROUP BY With ORDER BY together

This one is odd...
I have an hql query that ignore the GOUP BY if ORDER BY is included.
it will perform the query without the group by,
but if I remove the order ...

14. Nhibernate GroupBy multiple and Count

First off I know GroubBy multiple properties has not been implemented yet. What I want to do is

SELECT count(*)
    SELECT this_.SubmissionDate as y0_
    FROM ...

15. hibernate select all projections group by

In hibernate Criteria specification, if I want to group property in table, I must use Projections.groupProperty. The problem is I can't select all the field in the table. What I want ...

16. hibernate group query for interval in long

I have three entity


     opt name

     - response time (long-in millisec)
     - question option
in this ...

17. How to get SELECT values and COUNT value with JPA GROUP BY?

If my entity is get as a Man, it have name, id properties, with JPA how I get retrieve result like this query,

entityManager.createQuery("SELECT AS name, COUNT(m) AS total FROM ...

18. Using GROUP BY in JPA

I am having a @ManyToMany relationship between a Question entity and a Category entity. I want to count the number of questions in each category. How would I do that?

19. hibernate how to make a count and group by

Hi! I'm new using Hibernate and I want to make this query in hibernate: select count(*) from (select ID from myTable where fieldId =196135 group by ID) I wrote this code ProjectionList projList = Projections.projectionList(); DetachedCriteria bolgsEntries = DetachedCriteria.forClass(myTable .class).add(Restrictions.eq("fieldId ", new Long(id))) ; projList.add(Projections.groupProperty("id")); bolgsEntries.setProjection(projList); List listTenders = pm.getSession().createCriteria(myTable .class) .setProjection( Projections.projectionList() .add( Projections.rowCount() ) ) .add( Subqueries.propertyIn("id", bolgsEntries)) .list(); ...

20. Group by desc in jpql

I am having issues in using the 'group by' clause in JPQL. I need to fetch all the records grouped by their parent records, but need to group by the latest record.(grouping in reverse order) I tried running 'select * from table_name group by parent_record desc' on mysql and there was no problem. but when I tried it with JPQL, it ...

21. HQL group by ?

22. How to count the results of group by

Hi! I have this query: select count(job_id) from (select job_id from life where job_id=? group by plan_id) as tmptable; i need to count the amount of plan_id in life where job_id equals to something. Is there a better way to achieve it with hibernate? i use createQuery and put the properties of the classes

23. Having clause without a group by clause in HQL?

I tried SQLQuery but from the looks of it the way I'm using having (as a secondary where) is not appropriate for SQL. Instead I removed one of my indexes and now I'm only using one... this way my more frequent queries always run in sub-second speed while my less important queries take 30+ seconds... but hey, gotta compromise sometimes.

24. how to do the group by and having in criteria.

You need to use projections to do this because there is no explicit "group by" necessary in a criteria query. Certain projection types are defined to be grouping projections, which also appear in the SQL group by clause. An alias can be assigned to a projection so that the projected value can be referred to in restrictions or orderings. Here are ...

25. Projections group, rowCount & Transformers.aliasToBean

Hi. Excuse my English. I have a list of books and I group theese books by author. And, sure, I would like to have a way to read how many authors per book exists in the same result. So, I'm using Projections and the method rowCount(). That's fine, despite the fact I have to handle - there's more properties in the ...

26. Hql join and group by problem

Hibernate version: 3.6.0 Hello, I am trying to make a query in hql. I have these beans: Bean1 Code: @Entity @Table(name = "bean1") public class Bean1 { ... ...

27. Group By Query with Criteria API

Hi all, I have a table which consists of Composite key made up of three columns . Let the columns be A,B,C .Now the situation is to have groupby on three combinations 1] A,B 2] A,C 3] A Can this be done through multiple projections on single Criteria call. What are the other options. Thanks in advance.

28. Count(*) on a group by subquery

29. Criteria Group By Problem

Hello, I'm so confused with Group By using Criteria by reading tips/articles on the internet, so I hope someone can help me here... I have a table: City, Name, Donation ------------------------ Jakarta, Helen, $20 Jakarta, Boy, $5 Jakarta, Helen, $10 Bandung, Rudy, $3 Bandung, Linda, $4 I can group by the City and sum the Donation so that the table like ...

30. Group By not working ..hmmm...

31. HQL and GROUP BY

Hi all, When I try to execute the HQL below... Code: SELECT as date, m.documentNumber as documentNumber, m.amount as amount, m.description as description, m.accountTaxValue as accountTaxValue, m.account as account, (SELECT sum(amount) from Movement where date > '20-02-2011') as primo FROM Movement m GROUP BY ...

33. hibernate subqueries: group by average

Hey there, I have the the following problem: the basic idea is to fetch sorted list of objects (from tableA) ordered by the price average of corresponding items from tableB (tableA 1:n TableB). i.e. tableA has multiple items, each of which has multiple prices associated, recorded on tableB. The SQL (below) works fine, but I'm struggling to get the same results ...

34. MySQL "group by" error

I have the following HQL query: select, count( from AccessLogEntry entry group by order by count( desc On mysql, it fails with a generic error, and a message from server: "Invalid use of group function". If I remove the order by clause, the query works fine. I suspect this is related to the fact that MySQL won't accept an ...

35. Not working: SQL Subquery using "Group By" stateme

Hi I'm having a problem running this SQL statement below. Can anyone help me? select outage from outage in class where outage.outageId in ( select MAX(poutage.outageId) FROM poutage where poutage.ticketNo > 0 GROUP BY poutage.ticketNo) and and plannedoutage.po_ou tage_status='In Progress' is throwing an Exception as below 14:44:24,906 WARN [JDBCExceptionReporter] SQL Error: 907, SQLState: 42000 14:44:24,906 ERROR [JDBCExceptionReporter] ORA-00907: missing ...

36. Group by problem

37. group by month with hibernate ?

Thank you very much for your answer dimas ! Your tip seems correct to me but I am not able to make it working. It currently fails at hibernate layer. Here is what I did and the trace error is at the bottom. Should I use something else than session.find(...) for DB specific queries (Oracle 9i in my case) ? session.find("select ...

38. Group-by query oddness

I am doing a group-by query using Hibernate 2.1.2 written in HQL. The underlying database is Postgres 7.3. My query is defined thus: select m.locationCode, m.account, m.subAccount, sum(m.amountInCents), max( \ from com.fubar.MeglAggregate m \ group by m.locationCode, m.account, m.subAccount \ order by m.locationCode, m.account, m.subAccount I guess I have gotten it formatted correctly, because the query DOES return the expected data. ...

39. Group by Problem in HQL

Hello Every body, Here is my simple HQL select, sum(sl.quantity),sum(sl.bookedQuantity) from DepotDTO d,DepotInventoryDTO di,StoreDTO s inner join s.storeLines sl, ProductDTO p where d = di.depot and d.code = 'DSD' and di = s.inventoryHouse and sl.product = p group by It works fine. Now i want to execute the following HQL (Slight different except selecting I select whole p ...

40. Group by Problem in HQL

41. Group by object does not work

I have been trying to group by an object as in the following example code from the reference page. select cat, count( elements(cat.kittens) ) from eg.Cat cat group by cat The sql that is generated only ever groups by the id of the object instead of each of the fields selected to create the object. Obviously this causes sql exceptions stating ...

42. count on group by

I'm trying to do something like this: I've got a cat table, each cat can belong to a certain type of cat, I would like to do a select where I group by cat.type and also count the number of cats that belong to each type. Has anyone done something like this before?

43. Group by doubts

44. group by Day

Hi, Hibernate version: 2.1.4 Name and version of the database you are using: Oracle 9i I have the following query: Code: select, o.orderDate, count(*) from Order o group by, o.orderDate I need to group by date (o.orderDate), but the orderDate is a Calendar. Code: ...

45. Need help with group-by query. I'm stuck

Author Message egervari Post subject: Need help with group-by query. I'm stuck Posted: Fri Aug 27, 2004 9:03 am Senior Joined: Sun Oct 26, 2003 5:05 am Posts: 139 I really need some help with this query. I tables A (RnsTransaction) and B (RnsTransactionHistory) and B has a boolean that I'd like to count all the A's that have ...

46. Can someone show me how to do a group by clause query?

I need an query example to do something like this SELECT taskno, count(taskno) from ACTIVITY group by taskno order by taskno Where ACTIVITY has a composite id in a ACTIVITYPK java class that is made up of these three properties: userid, date & taskno How do I tell Hibernate that taskno is a property of ACTIVITYPK? Here is the trace log: ...

47. Using GROUP_BY with Criteria API?

48. How to count the results of a query with GROUP BY?

I am searching for a way to count the number of rows that would be returned by a query with a group by clause. For example a count(*) around this query: select NAME, count(AGE) from PERSON where NAME like :nameParam group by NAME This does not work (SQL semantics): select count(*) from PERSON where NAME like :nameParam group by NAME In ...

49. count in group by or order by (new parser in h3 beta1)

Hibernate 3 beta1 (new parser) don't recognize count in group by or order by clause count work in select clause This hql don't work with new parser (it work with old parser) select,min(bid.created),count(*) from Bid bid group by having min( > 1 order by,count(*) or select,min(bid.created),count(*) from Bid bid group by having count( > 1 order ...

50. Group By along with Order By results in incorrect SQL

Hibernate version: 2.1.4 Name and version of the database you are using: Cloudscape 10 The following HQL: SELECT rights.featureName, rights.featureVersion, rights.vendorDaemonName, sum(rights.seatCount) FROM com.macrovision.flexnet.manager.entities.LicenseRights AS rights WHERE rights.licenseFile.licenseServer.master=1 GROUP BY rights.featureName, rights.featureVersion, rights.vendorDaemonName ORDER BY rights.featureName, rights.featureVersion, rights.vendorDaemonName Results in SQL that does not work in Cloudscape select licenserig0_.featureName as x0_0_, licenserig0_.featureVersion as x1_0_, licenserig0_.vendorDaemonName as x2_0_, sum(licenserig0_.seatCount) as x3_0_ from ...

51. GROUP BY parser error

net.sf.hibernate.QueryException: BY expected after GROUP or ORDER: where [select TO_CHAR(order.sentdate, 'MM'), sum( order.ordertotal ) from cinetica.ejb.cart.OrderData order where TO_CHAR(order.sentdate, 'YYYY') = ? group by TO_CHAR(order.sentdate, 'MM')] at net.sf.hibernate.hql.ClauseParser.token( at net.sf.hibernate.hql.PreprocessingParser.token( at net.sf.hibernate.hql.ParserHelper.parse( ...


"select distinct p, count(ol) " + "from Order d " + "join d.lines ol " + "join ol.referenceProduct p " + "join p.options o " + "join p.catalogs c " + "join p.parentBrand b " + "where o.stockAvailable > 0 " + "and d.creationDate > :date " + filter + "group by, p.regularPrice, p.reference, p.buyPrice, " + "p.creationDate, p.lastModification, p.bargain, ...

53. HQL group by

Need help with Hibernate? Read this first: ... AskForHelp Hibernate version: 2.1.7 hello, I have a general question. I want to write a query similar to this. sql query: select count(Distinct userid) as users, truckid from unitofwork where Zoneid='Z101' and truckid is not null group by truckid - what are my options ot handle this in hibernate. 1. I tried ...

54. Can't query views that contain a GROUP BY.

56. count in HQL query that includes group by and having count

Hi, We are running the following query with HQL (it includes a main table and a set table inlcuded). select distinct from com.bmc.idm.ctsa.hb8.dbobject.Request as request join request.fields as fields where ((fields.attrName = 'jc_name' and fields.attrValue = 'xyz1') or (fields.attrName = 'user_id' and fields.attrValue like :'abc1') ) group by having count(fields) = 2 We would like to execute select count ...

57. HQL: Can't "group by" object

select,, count(d) from Cat a left join a.girlfriends b left join a.kittens c left join d with d in elements( group by, order by, ...

58. Problem with query using GROUP BY and ORDER BY

I am using Hibernate 3.1 with MySQL and Java and I am trying to make a simple query to get the rows from table that appear more times in that table. For example, suppose I have a table 'Sales' that stores the sales made by employees to customers. This table has two columns: int customerId int employeeId Each row stores which ...

59. Group By ... Having ... - Possible?

60. Group By HQL produces buggy SQL

Hibernate version: 3.0.5 Mapping documents: Name and version of the database you are using: MySQL HQL : Hibernate: /* select,,count(distinct StudentEnrollment.enquiryId) from StudentEnrollment as StudentEnrollment group by, */ Generated SQL (show_sql=true): select discoverys3_.Id as col_0_0_, newspaper6_.Id as col_1_0_, count(distinct studentenr0_.EnquiryId) as col_2_0_ from studentenrollment studentenr0_, studentenquiry studentenq1_, enquirydiscoverymapping discoverys2_, discoverysourcemaster discoverys3_, enquirynewspaper newspapers5_, newspapermaster newspaper6_, enquirydiscoverymapping discoverys8_, discoverysourcemaster discoverys9_, ...

61. group by using the Criteria api ?

Hi, I am using hibernate 3.1.3 with Oracle version 9.2 This is my basic query... Criteria criteria = session.createCriteria(className).add(Example.create(obj).excludeZeroes()); List ret = new ArrayList(); ret.addAll(criteria.list()); ...where 'obj' is a class with mappings this works, but if I have multiple versions of 'obj' stored as new rows in the db table , I get them all back in my response ( I ...

62. Criteria having and group by?

64. group by not working

Hibernate version: 3.1 Name and version of the database you are using: MySql 5 Hi, I have problem with following HQL query: s.createQuery("select,g.klucze.wartosc,count(distinct g) from Elem g" + " where datediff (sysdate(), <= :dni and" + " g.skasowany='' and g.klucze.blokada=''" + " group by,g.klucze.wartosc order by 3 desc"); the generated sql: select as col_0_0_, klucz4_.wartosc as col_1_0_, count(distinct ...

65. GROUP BY and Table per Concrete Class

public abstract class X { private Long OID; private Integer xAttr; private String status; ... } public class Y extends X { Integer yAttr; ... } public class Z extends X { Integer zAttr; ...

66. Problem with GROUP BY clause

Hi I need to use Hibernate 3.1.3 to create a query that returns an object and a aggregate value like this: select max (t.test_date), t from TestTo t. Because I have an aggregate function I have to define the GROUP BY clause: select max (t.test_date), t from TestTo t group by t But I get an error: ORA-00979: not a GROUP ...

67. Criteria API -- group by?

I have an object with a one-to-many relationship, for instance Cat to paws. When i use the Criteria API on Cat it does a join on Paws and returns multiple rows for each Cat. In HQL I would (a) not join on Paws, or if i had to I would (b) do a group by on each field in Cat. How ...

68. Group By not translated HQL -> SQL

Hibernate version:3.1.3 Name and version of the database you are using:oracle 10.1 Spring 1.2 (hibernateDAOSupport) Hi, When i execute this part of code : requete = requete.append(" SELECT new com.truc.machin.srv.domain.wrapper.FileDetailsEFWrapper(count(factrace.Fact.Id),factrace.Fictra.Jalon.JalonLib,factrace.Fictra.FictraDtPassage,factrace.Fictra.FictraEtat,facetat.FacetatLib,factrace.FactraDtReponseAmc,anomalie.Id,anomalie.AnoLib) ") ; requete = requete.append(" FROM Factrace as factrace LEFT JOIN factrace.Facetat as facetat LEFT JOIN factrace.Ano as anomalie ") ; requete = requete.append(" WHERE factrace.Lot.Flux.Fic.Id=:ficId AND factrace.Fictra.Jalon.Id = (SELECT MAX(factrace2.Fictra.Jalon.Id) ...

69. group by for a newbie - sos

Hibernate version: 2 can you use hsql to "select" and "group by" without specifying all of the fields in the select and the group by? i have to retrieve 280 fields from three tables/objects (mapped many-to-one, many-to-one, many-to-one).... specifying all the fields explicitly, i get a huge sql statement which is bombing in db2... e.g.: select obj1.obj2.obj3.lotsoffields (...), obj1.obj2.lotsoffields (...) from ...

70. join, group by, sort by, help needed

hi, I am new to hibernate and need some help I have a table called customers (id(primary key), customer name, purchase date, and receipt number(foreign key)) and another table called purchases (receipt number(primary key), details, amount) the mapping is done correctly (many to one) I want to find out the most recent purchases for each customer with the amount shown. So, ...

71. Group by Query order by the sum value result

72. Group By problem

73. SQLServerDialect GROUP BY problem

74. hql group by

75. hql group by clause help needed

i am trying to use "group by" in a new way and don't understand why it doesnot work for me. can somebody help me on this one? this one works select from atable atable group by this one works too select atable.aforeignkey from atable atable why this one doesnot? select atable.aforeignkey from atable atable group by atable.aforeignkey Thanks


Hallo, maybe i do not understand the correct usage of Projections: what I would like to do is something like: Code: SELECT count(*) FROM (SELECT this_.ADDRESS_PROVINCE_CODE as y0_, ...

77. Doubt about group by using Criteria

Hi all ! I have a doubt about a criteria that I did. This is the code that I need. select sum(table_name.field_1) from Table As table_name group by table_name.field_2 When I use the "group by" clause, NHibernate put the field_2 on my "Select". I just want like above. Thanks Thiago (from Brazil)

78. projection, group by, not single column

Hi, I'm new to Hibernate and I've got a problem: I've three table: Table 1:

79. Criteria query with join and group by

80. group by with HQL

I am trying to retrieve latest version of rows and following SQL works for me select id, max(version), marketplace, package from mytable where marketplace = 'XXX' and package = 'YYY' group by marketplace, package order by version desc But I am unable to get equivalent HQL working, e.g. from mytable m1 where m1.version = (select max(m2.version) from mytable m2 where m2.marketplace ...

81. Criteria GROUP BY

My Database is SQL Server 2005 and here is how I have achieved. Code: Session session = somehow get session. Criteria criteria = session.createCriteria(TaskTypeFunction.class, "classone").setProjection( Projections.projectionList().add( ...

82. Group By and JPQL

83. Help with Group by operation ... URGENT PLSS

I am a beginner to hibernate . I am having trouble in using GROUP-BY operator using detached criteria queries. I have two tables: 1) Transactions: {ID, present, paymentRecord} 2) Records: { ID, name, content} Transactions and records have many to one relation ship I want to select all the transactions which have present = false and grouped by paymentRecord I used ...

84. "Group By - Having Count" with criteria

85. Group By Clause