aggregate « Query « JPA Q&A





1. SQL query throws "not in aggregate function or group by clause" exception    stackoverflow.com

I'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.com

I 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.

SELECT
 client,
 clientCampaign,
 count( formData )
FROM
 ...

3. Ordering by a Column that's not in the Group By or Enapsulated in an Aggregate    stackoverflow.com

I 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.com

I would like to perform the following query in HQL:

select count(distinct year(foo.date)) from Foo foo  
However, this results in the following exception:
org.hibernate.hql.ast.QuerySyntaxException: expecting ...

5. Confused about distinct/aggregate queries with (N)Hibernate    stackoverflow.com

I'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.com

I'm new to ORM stuff and I need some help understanding something. Let's assume I have the following standard SQL query:

SELECT *, COUNT(test.testId) AS noTests FROM inspection
LEFT JOIN test ON inspection.inspId = ...

7. avg aggregate function in HQL causing trouble in NHibernate, what is wrong with my query?    stackoverflow.com

Having 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.com

Trying to run the following HQL with NHibernate:

select count(distinct t) as TweetCount
from Tweet t
    join t.Tweeter u
    left join t.Votes v
    left ...

9. Querying aggregate object in NHibernate/Hibernate    stackoverflow.com

I 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.com

I 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.com

Given 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.com

Querying by HQL with

select a from Article a join a.tags t 
where t.name in (:tags) 
group by a 
having count(t)=:tag_count
using HSQLDB in Grails produces an SqlException "Not in aggregate function...". Looking ...

13. JPA select new with aggregate function on member collection    stackoverflow.com

I have a query like the following:

Select new mypackage.MyClass( u, max(sc.serviceDate))
    from Unit u left join u.serviceCalls sc
    where u.organization.key = :organizationKey
So, my mapping is ...

14. Java Hibernate JPQL Query (Aggregate Function: count)    stackoverflow.com

During a join I get the following Table (example):

+----------+----------+
| Hostname | Severity |
+----------+----------+
| host1    |   high   |
| host2    |  medium ...

15. HQL: Trouble with trivial select with aggregate function    stackoverflow.com

I have very simple data model like this:

create table Company (
    id int primary key,
    name varchar(50),
    street varchar(50)
)

create table Person (
 ...

16. Aggregate function in HQL    coderanch.com

hi 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.com

It'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.org

hi 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.org

hi 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.org

Hi 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.org

Is 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.org

hi, 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.org

Hi, 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 ...

30. Aggregate functions fail if querying super class    forum.hibernate.org

select 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.org

Hi 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

36. NonUniqueResultException from an aggregate query    forum.hibernate.org

37. HQL: order by clause with aggregate function    forum.hibernate.org

hello 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.org

Hi, 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.org

Query 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.org

Hi 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.org

I'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. marker_dto_hi_value marker_dto_next_value 100 etc I get ...

44. not able to group by object alonside aggregate function    forum.hibernate.org

Hibernate 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.org

What 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.org

Hibernate 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.org

Hi, 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.org

I 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.org

Hi 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.org

i 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.org

Hi 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.org

I 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.org

In 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.org

Budy, 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.org

Hello, 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.org

Hi 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. org.hibernate.dialect.MySQLDialect org.hibernate.dialect.MySQLInnoDBDialect org.hibernate.dialect.MySQLMyISAMDialect Exception Got: ***************** SQLException.(SQLGrammerException) Note: ****** ...

61. Subquery used twice for aggregate functions    forum.hibernate.org

Hi, 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.org

Hibernate version: 3.3.1 GA Mapping documents:

64. Errors with aggregate in order by clause    forum.hibernate.org

Hi, 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