1. SQL query throws "not in aggregate function or group by clause" exception stackoverflow.comI'm working on repairing the test suite for a project of ours, which is being tested through Hibernate/DBUnit. There are several test cases which all throw a similar exception from ... |
2. Count function in HQL causing null results? stackoverflow.comI have the following HQL query that is attempting to return 2 object instances as well as an aggregate count based upon a 3rd object instance.
|
3. Ordering by a Column that's not in the Group By or Enapsulated in an Aggregate stackoverflow.comI have a problem getting this JPA query to work on MS SQL Server 2008. The background is as follows: Users create jobs for clients, of which there are many. I am ... |
4. Using functions as arguments in hibernate aggregation functions stackoverflow.comI would like to perform the following query in HQL:
However, this results in the following exception:
org.hibernate.hql.ast.QuerySyntaxException: expecting ... |
5. Confused about distinct/aggregate queries with (N)Hibernate stackoverflow.comI'm not sure how to approach queries that don't map 1:1 to my persistent entities - in other words, distinct and aggregate queries. For example, I need to retrieve a distinct ... |
6. JPA and aggregate functions. How do I use the result of the query? stackoverflow.comI'm new to ORM stuff and I need some help understanding something. Let's assume I have the following standard SQL query:
|
7. avg aggregate function in HQL causing trouble in NHibernate, what is wrong with my query? stackoverflow.comHaving trouble with an HQL query. If I remove the avg(..) from it it will return all of the empty weights, so the trouble is with the avg function. It seems ... |
8. NHibernate HQL - select count(*) with having - can't get it to work stackoverflow.comTrying to run the following HQL with NHibernate:
|
9. Querying aggregate object in NHibernate/Hibernate stackoverflow.comI have a Domain Model for Countries and States that looks like the following(see below). I want to (with the Crieteria API or HQL) fetch all States for a specific ... |
10. Using Aggregate functions in JPA stackoverflow.comI have the folowing situation. I want to retrieve a list of bids between time t1 and time t2. Then from this list i want to retrieve winning bid i.e maximum bid ... |
11. Help with HQL: Aggregate (count) stackoverflow.comGiven the model Activity containing a bag with models of type Report (one to many). I would like to get a list of all activities, containing the number of reports of ... |
12. Grails: "Not in aggregate function" problem with HQL query stackoverflow.comQuerying by HQL with
using HSQLDB in Grails produces an SqlException "Not in aggregate function...". Looking ... |
13. JPA select new with aggregate function on member collection stackoverflow.comI have a query like the following:
So, my mapping is ... |
14. Java Hibernate JPQL Query (Aggregate Function: count) stackoverflow.comDuring a join I get the following Table (example):
|
15. HQL: Trouble with trivial select with aggregate function stackoverflow.comI have very simple data model like this:
|
16. Aggregate function in HQL coderanch.comhi all i am using the below query in my program.. select rsrcrt.comp_id.rsrcId, count(rsrcrt.comp_id.rtCd), sum(rsrcrt.rtValCd) from TwaevntRsrcRt rsrcrt group by rsrcrt.comp_id.rsrcId here when i am retrieving the value using query.list, it returns the object, the problem is the object is not of type TwaevntRsrcRt as expected. and also i tried to create a new class having the values listed in select ... |
17. Help with HQL using aggregate function in where/having clause coderanch.comIt's for a report that will list items' situation in a specific date. So I only want items transferred (last transfer) to that unit until that date. But I realized I'll have to use SQL instead. This is just part of the query. I'll need union and minus, and HQL doesn't implement that =/ |
18. Hibernate :Problem with Aggregate Functions forum.hibernate.orghi i am trying to run my application. String hqlquery="select min(invAmt) from Insurance insurance"; Query query=session.createQuery(hqlquery); List list=query.list(); but this is giving me an exception when i run it No data type for node: org.hibernate.hql.ast.AggregateNode \-[AGGREGATE] AggregateNode: 'min' \-[ALIAS_REF] IdentNode: 'invAmt' {alias=invAmt, no from element} Is this the problem due to jar files ?? I have installed all the corresponding jar ... |
19. Hibernate:Aggregate Functions forum.hibernate.orghi i am trying to run my application. String hqlquery="select min(invAmt) from Insurance insurance"; Query query=session.createQuery(hqlquery); List list=query.list(); but this is giving me an exception when i run it No data type for node: org.hibernate.hql.ast.AggregateNode \-[AGGREGATE] AggregateNode: 'min' \-[ALIAS_REF] IdentNode: 'invAmt' {alias=invAmt, no from element} Is this the problem due to jar files ?? I have installed all the corresponding jar ... |
20. What is the aggregate function return type? forum.hibernate.org |
21. Aggregate functions show results of wrong table joined twice forum.hibernate.org|sf.roster.id|ga.id|sf.finalScore|sa.finalScore| |------------|-----|-------------|-------------| | 1| 3| null| null| | 1| 5| ... |
22. Does HQL/JPAQL provide a string aggregate function? forum.hibernate.orgHi Hibernate/JPA gurus, I am struggling to find a way using which I can get concatenate the values of a particular column from multiple rows as one comma separated String. If anyone can tell me a solution to it, it will give me a hell lot of a relief because it has become an obstacle for me. I am using Entity ... |
23. Ordering by aggregate functions using HQL forum.hibernate.orgIs it possible? I use the GROUP BY clause and select some counts, sums etc. I need to order the list according to these values. I've tried query in the form SELECT count(x) FROM t GROUP BY y ORDER BY count(x) but I've got java.sql.SQLException: General error: Invalid use of group function After that I've tried following query: SELECT count(x) as ... |
24. PROBLEM WITH AGGREGATE FUNCTION RETRIEVAL forum.hibernate.orghi, I'am a starter in the hibernate arena. Please help in the following problem. I'am facing a problem while using aggregate functions. I'am using the example that is given in the documentation. Query q = session.createQuery( "select blog.name,count(blog) from eg.Blog as blog group by blog.name"); List l=q.list(); String b=(String)l.get(0); System.out.println("count"+b); here I'am facing a serious problem . I'am getting class cast ... |
25. My aggregate function forum.hibernate.org |
26. HQL with aggregate function forum.hibernate.org |
27. Native SQL Queries - Aggregate Functions forum.hibernate.org |
28. Ordering by Aggregate function: revisited forum.hibernate.orgHi, I know this topic has been discussed to death but I see no viable solution and the only semi-work-around I see have no real explanation so I was hoping somebody would enlighten me with the answer. Thanks. The HQL I need to run is, "select u.name, count(*) ct from user u group by u.name order by ct desc" After searching ... |
29. Getting the return value of an Aggregate functions forum.hibernate.org |
30. Aggregate functions fail if querying super class forum.hibernate.orgselect news0_.id as id, case when news0__1_.news is not null then 1 when news0__2_.news is not null then 2 when news0_.id is not null then 0 end as clazz_, news0__1_.game as game30_, news0__2_.game as game31_ from news news0_ left outer join game_news news0__1_ on news0_.id=news0__1_.news left outer join game_news2 news0__2_ on news0_.id=news0__2_.news where (case when news0__1_.news is not null then 1 ... |
31. Simple question of order by aggregate function forum.hibernate.orgHi All , I have an HQL like this : select po, avg(item.price) from foo.PO po join po.items item group by po How can I have the po sort by avg(item.price) ? I tried to found google but can't get the answer of this simple sql question. Please help .... Thanks. Perseus |
32. order by aggregate function? forum.hibernate.org |
33. aggregate function forum.hibernate.org |
34. aggregate functions forum.hibernate.org |
35. returning full object from a query using aggregate function forum.hibernate.org |
36. NonUniqueResultException from an aggregate query forum.hibernate.org |
37. HQL: order by clause with aggregate function forum.hibernate.orghello I was wondering why the folling query gives crashes with the message "Invalid use of group function" (MySQL) When I order the query by c.ID everything works fine, but I can't order it by an aggregated field. Does anyone know why and how it should work??? The query already looks like the examples in the Hibernate documentation... select c, max(log.date), ... |
38. aggregate function expected before ( in SELECT forum.hibernate.org |
39. Aggregate functions on collections forum.hibernate.orgHi, I'm having trouble with a tricky aggregate HQL query. I am querying for Item records that have collections of Keyword. I would like to count the total number of keywords found across the set of Item objects found. My standard query looks like.. "from Item a join a.keywords k where a.title like ? or a.description like ?" I want to ... |
40. Problem with aggregate function sum(colA*colB). forum.hibernate.orgQuery query = session.createQuery( "select stocks.editor.name, sum(stocks.noItems * stocks.priceOld) from stocks in class org.vasiliada.dbo.Stock where stocks.editor.editorId = 5 group by stocks.editor.editorId"); ScrollableResults result = query.scroll(); assertNotNull(result); result.beforeFirst(); ... |
41. Perform aggregate count and encapsulate into value object forum.hibernate.orgHi guys. I am new to Hibernate and just started playing around with it yesterday. Well I have to admit it is definitely one of the best persistence solution today. I need some help here though. The scenario is this: One Registrant registers for one GolfSession. One GolfSession can be registered by many registrants. I am trying to retrieve the count ... |
42. using sql aggregate function in HQL forum.hibernate.org |
43. Aggregate functions: sum forum.hibernate.orgI'm trying to run the following query on a MySQL InnoDB database (MySQL InnoDB dialect set) (just an example): select sum(m.id) from markers m where markers is a table for mapped entities and id is an integer field in that table. |
44. not able to group by object alonside aggregate function forum.hibernate.orgHibernate version: 2.1.8 Full stack trace of any exception that occurs: 21/09/2005 14:31:05 net.sf.hibernate.util.JDBCExceptionReporter SEVERE: ERROR: column "accountdat0_.accounttype" must appear in the GROUP BY clause or be used in an aggregate function Problem definition: I am having problem with grouping by an object in a query. As far as I know this is not achievable automatically. e.g. SELECT account, sum(trx.amount) from ... |
45. Aggregate query : HQL vs SQL forum.hibernate.orgWhat is the performance implication of using HQL aggregate query vis-a-vis native SQL aggregate query ? For example if I am to get the average age of large population stored in database. Lets assume no of record is large. My understanding is that HQL will fetch all people objects in client and compute the average age there. Whereas, in case of ... |
46. Join madness when using aggregate functions. forum.hibernate.org |
47. Order by an aggregate query forum.hibernate.orgHibernate version: 3.1.3 Consider this scenario: - Entity A with one-to-many relations to Entity B and to Entity C (simple parent-child relation). - Lots of Bs and Cs for each A (hundreds), and lot of As (tens of thousands). - B and C both have one property, let's call it "value". What's a good way to do a HQL query to ... |
48. how to do aggregate HQL query properly forum.hibernate.orgHi, i wouldlike to use aggregate functions in HQL, and i got such problems while experimenting with the query... i have a class Booking like Booking(id, tutor,week,package,date,fee,term,note,student,order) i wouldl like to do a query as such : " "SELECT DISTINCT tutor, week, COUNT(DISTINCT student) AS numOfStudents FROM Booking WHERE term=:tm GROUP BY tutor,week" problem 1 is - the query does not ... |
49. Aggregate function in where clause (Criteria API) forum.hibernate.orgI am using Criteria API. I want to use the Aggregate Functions like Avg(),count() etc in the where clause i know its available through Projections but projections used as the slect part of the query i want to use these functions in where clause like criteria.add(Restriction.eq(Projections.count("emp"),5)); Is there any way to do that. if the problem is not clear lets say ... |
50. Need help using count (aggregation) forum.hibernate.orgHi all. Did some searching, couldn't find answer to total newbie question. Hibernate version: 3.1 I'm using Eclipse and Java 5. I have one question and one comment: 1. How do I use the count function. 2. Criticism of section 7.4.2 of Hibernate in Action 1. After reading chapter 7, I tried writing this code to get a count of records ... |
51. How to use aggregate function sum() using SqlQuery forum.hibernate.orgi have used the aggregate function sum() as follows: query.append("select oe.name,p.productID,p.partNumber,sum(i.quantity) as quantitysum from products p" + " join inventoryitems as i on p.productID = i.productID" + " join inventoryowners io on io.inventoryitemid = i.inventoryitemid" + " join OEMs as oe on p.OEMID = oe.OEMID + " where i.statusCode in ('NEW', 'ACT', 'PHOLD', 'INACT') "); SQLQuery sqlQuery = currentSession.createSQLQuery(query.toString()); sqlQuery.addScalar("oe.name", Hibernate.STRING); ... |
52. Query with criteria : problem with aggregation forum.hibernate.orgHi everybody, I'm testing the power of hibernate and the query building with criteria. I built a test database with Book (id, title), Author(id, name), and an association table because some Book are written by several Author ! I mapped and logically, the class Book.java and Author.java contain respectively a Set of Author and Book. I wrote this piece of code ... |
53. Hibernate 3.2 aggregation function change forum.hibernate.org |
54. Aggregation and Criteria queries forum.hibernate.org |
55. Native SQL TIP: Using aggregate queries forum.hibernate.orgI did not see this in new book, doc or forum, so thought that I would post my expereince with Oracle. Parent child relationship between itinerary and res_core. The test query with a hardcoded ID is: select itinerary_id as ITINERARY, sum(rc.grand_total) as TOTAL, max(rc.total_night_stay) as NIGHTS, min(rc.arrival_date) as ARRIVAL , max(rc.departure_date) as DEPARTURE from res_core RC where itinerary_id = 14679 and ... |
56. Usage of aggregate function with HQL forum.hibernate.org |
57. How to use aggregate function in order by with CRITERIA API? forum.hibernate.orgIn the Hibernate documentation for HQL the following is shown and I want to do this with the CRITERIA API. ======================================= SQL functions and aggregate functions are allowed in the having and order by clauses, if supported by the underlying database (eg. not in MySQL). select cat from Cat cat join cat.kittens kitten group by cat.id, cat.name, cat.other, cat.properties having avg(kitten.weight) ... |
58. Ordering a query by a Aggregate column forum.hibernate.orgBudy, Ive seen the documentation, but , there are just a few examples ordering queries with properties. In my case, Im selecting just a aggregate column. Ive seen some other examples with "count columns", and maybe the problem is with that distinct (inside the count). Something like "Select count(cli.codCli) from OrdVis order by count(cli.codCli)" , and that just runs fine ... ... |
59. using aggregate function forum.hibernate.orgHello, I am using hibernate 3 I have a table which contains id Integer product_name Varchar(50) category_id Integer stock Integer rate decimal I need to get the product which has max(rate). I know I can use the hibernatetemplate.find( query ) to get it. But I don't know how to design a Java class for this purpose? I mean the getter and ... |
60. Problem in using Aggregate function forum.hibernate.orgHi all, I am getting exception while tyring to use Aggregate function as follows in Hibernate3.0. String SQL_QUERY = "select SUM(price) from customer_account"; SessionFactory sessionFactory = HibernateUtil.getSessionFactory(); Session session = sessionFactory.openSession(); Query query = session.createSQLQuery(SQL_QUERY); Configuration Details: ************************ I am using Hibernate3.0 with MYSql Database. |
61. Subquery used twice for aggregate functions forum.hibernate.orgHi, I have following pseudo SQL query in my code: Code: INSERT INTO (A, B, C, D) SELECT E, F, (SELECT COUNT(*) FROM X WHERE X.ID=Y.ID) AS G, _H_ FROM Y WHERE [CONDITION] Problem I have is that in place of _H_ I would actually like to write 'G + 100'. But if I do it then in normal SQL (SQL ... |
62. Aggregation using projection and criteria forum.hibernate.org |
63. sql-query with join and aggregate forum.hibernate.orgHibernate version: 3.3.1 GA Mapping documents: |
64. Errors with aggregate in order by clause forum.hibernate.orgHi, I'm having errors with an aggregate function (min) in my order by clause. I don't understand the error message, and I've tried so many other ways to do this in HQL... I don't even think its possible using criteria, I just get all kinds of crazy errors. I'm just so frustrated. I got this far with some help on the ... |
65. Novice question - HQL Aggregate Queries forum.hibernate.org |