1. What adavantages does HQL have over SQL for Group By queries stackoverflow.comI 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 stackoverflow.comI have the following class structure FlowerDAO with the fields (mapped using Hibernate):
|
3. Add a Group by to a Hibernate Criteria-Query without Projection stackoverflow.comI 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 stackoverflow.comI'm trying to get a report using Criteria and ProjectionList, and I'm pretty new using this through hibernate. So I have this model: |
5. JPQL / SQL: How to select * from a table with group by on a single column? stackoverflow.comI 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? stackoverflow.comI 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: |
7. Using group by and having in a JPA query stackoverflow.comHow would you do the following using the JPA query language? |
8. Help with HQL query (group by, order by) stackoverflow.comI have problem writing HQL to display distinct applicationId with the latest application (newest createDate) for the following data in the table. |
9. Hibernate Criteria: Projecting Count with group by clause stackoverflow.comI want to execute the following SQL |
10. Hql join and group by problem stackoverflow.comI am trying to make a query in hql. I have these beans: Bean1 |
11. JPQL: efficient way to use GROUP BY stackoverflow.comI need to do the equivalent of this efficiently in JPQL: |
12. Can I use group by in a subquery? stackoverflow.comI want to do the following: |
13. Hibernate - cant use GROUP BY With ORDER BY together stackoverflow.com
|
14. Nhibernate GroupBy multiple and Count stackoverflow.comFirst off I know GroubBy multiple properties has not been implemented yet. What I want to do is |
15. hibernate select all projections group by stackoverflow.comIn 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 stackoverflow.comI have three entity |
17. How to get SELECT values and COUNT value with JPA GROUP BY? stackoverflow.comIf my entity is get as a Man, it have name, id properties, with JPA how I get retrieve result like this query, |
18. Using GROUP BY in JPA stackoverflow.comI 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 coderanch.comHi! 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 coderanch.comI 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 ? forum.hibernate.org |
22. How to count the results of group by forum.hibernate.orgHi! 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? forum.hibernate.orgI 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. forum.hibernate.orgYou 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 forum.hibernate.orgHi. 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 forum.hibernate.orgHibernate 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 forum.hibernate.orgHi 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 forum.hibernate.org |
29. Criteria Group By Problem forum.hibernate.orgHello, 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... forum.hibernate.org |
31. HQL and GROUP BY forum.hibernate.orgHi all, When I try to execute the HQL below... Code: SELECT m.date 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 ... |
32. DISTINCT + GROUP BY + JPA 2 + Hibernate 3.6.7 Problem forum.hibernate.org |
33. hibernate subqueries: group by average forum.hibernate.orgHey 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 forum.hibernate.orgI have the following HQL query: select entry.host, count(entry.id) from AccessLogEntry entry group by entry.host order by count(entry.id) 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 forum.hibernate.orgHi I'm having a problem running this SQL statement below. Can anyone help me? select outage from outage in class uk.co.servicestatus.bo.PlannedOutage where outage.outageId in ( select MAX(poutage.outageId) FROM uk.co.servicestatus.bo.PlannedOutage 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 forum.hibernate.org |
37. group by month with hibernate ? forum.hibernate.orgThank 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 forum.hibernate.orgI 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(m.id) \ 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 forum.hibernate.orgHello Every body, Here is my simple HQL select p.id, 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 p.id It works fine. Now i want to execute the following HQL (Slight different except selecting p.id I select whole p ... |
40. Group by Problem in HQL forum.hibernate.org |
41. Group by object does not work forum.hibernate.orgI 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 forum.hibernate.orgI'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 forum.hibernate.org |
44. group by Day forum.hibernate.orgHi, Hibernate version: 2.1.4 Name and version of the database you are using: Oracle 9i I have the following query: Code: select o.customer.name, o.orderDate, count(*) from Order o group by o.customer.name, 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 forum.hibernate.orgAuthor 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? forum.hibernate.orgI 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? forum.hibernate.org |
48. How to count the results of a query with GROUP BY? forum.hibernate.orgI 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) forum.hibernate.orgHibernate 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 bid.item.id,min(bid.created),count(*) from Bid bid group by bid.item.id having min(bid.item.id) > 1 order by bid.item.id,count(*) or select bid.item.id,min(bid.created),count(*) from Bid bid group by bid.item.id having count(bid.item.id) > 1 order ... |
50. Group By along with Order By results in incorrect SQL forum.hibernate.orgHibernate 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 forum.hibernate.orgnet.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(ClauseParser.java:33) at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123) at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29) ... |
52. HQL: GROUP BY forum.hibernate.org"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.id, p.regularPrice, p.reference, p.buyPrice, " + "p.creationDate, p.lastModification, p.bargain, ... |
53. HQL group by forum.hibernate.orgNeed help with Hibernate? Read this first: http://www.hibernate.org/ForumMailingli ... 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. forum.hibernate.org |
55. How to specify GROUP BY ... HAVING using Criteria API ? forum.hibernate.org |
56. count in HQL query that includes group by and having count forum.hibernate.orgHi, We are running the following query with HQL (it includes a main table and a set table inlcuded). select distinct request.id 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 request.id having count(fields) = 2 We would like to execute select count ... |
57. HQL: Can't "group by" object forum.hibernate.orgselect a.id, b.id, count(d) from Cat a left join a.girlfriends b left join a.kittens c left join c.toys d with d in elements(b.toys) group by a.id, b.id order by a.id, ... |
58. Problem with query using GROUP BY and ORDER BY forum.hibernate.orgI 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? forum.hibernate.org |
60. Group By HQL produces buggy SQL forum.hibernate.orgHibernate version: 3.0.5 Mapping documents: Name and version of the database you are using: MySQL HQL : Hibernate: /* select StudentEnrollment.enquiry.discoverySources.id,StudentEnrollment.enquiry.newsPapers.id,count(distinct StudentEnrollment.enquiryId) from StudentEnrollment as StudentEnrollment group by StudentEnrollment.enquiry.discoverySources.id,StudentEnrollment.enquiry.newsPapers.id */ 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 ? forum.hibernate.orgHi, 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? forum.hibernate.org |
63. Simple group by, without getting back projection object forum.hibernate.org |
64. group by not working forum.hibernate.orgHibernate 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.id,g.klucze.wartosc,count(distinct g) from Elem g" + " where datediff (sysdate(),g.data) <= :dni and" + " g.skasowany='' and g.klucze.blokada=''" + " group by g.klucze.id,g.klucze.wartosc order by 3 desc"); the generated sql: select klucz2_.id as col_0_0_, klucz4_.wartosc as col_1_0_, count(distinct ... |
65. GROUP BY and Table per Concrete Class forum.hibernate.orgpublic 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 forum.hibernate.orgHi 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? forum.hibernate.orgI 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 forum.hibernate.orgHibernate 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 forum.hibernate.orgHibernate 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 forum.hibernate.orghi, 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 forum.hibernate.org |
72. Group By problem forum.hibernate.org |
73. SQLServerDialect GROUP BY problem forum.hibernate.org |
74. hql group by forum.hibernate.org |
75. hql group by clause help needed forum.hibernate.orgi 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 atable.aforeignkey.id from atable atable group by atable.aforeignkey.id 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 |
76. SELECT COUNT(*) FROM (SELECT .... FROM TABLE GROUP BY col)? forum.hibernate.orgHallo, 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 forum.hibernate.orgHi 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 forum.hibernate.orgHi, 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 forum.hibernate.org |
80. group by with HQL forum.hibernate.orgI 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 forum.hibernate.orgMy 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 forum.hibernate.org |
83. Help with Group by operation ... URGENT PLSS forum.hibernate.orgI 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 forum.hibernate.org |
85. Group By Clause forum.hibernate.org |