Frameworks| Hibernate| Struts| JSF| JavaFX| Ajax| Spring| DOJO| JDO| iBatis| Questions?

 

 

 

 

 

 

 

 

 

 

 

 

 

Search Tutorials:
 

Software Solutions and Services
 

 
  JDO Tutorials
  EAI Articles
  Struts Tutorials
  Java Tutorials
  Java Certification
  Java Applet
Questions
Comments
 
Hibernate Avg() Function (Aggregate Functions) 
 

In this section, we will show you, how to use the avg() function. Hibernate supports multiple aggregate functions.

 

Hibernate Avg() Function (Aggregate Functions)

                         

In this section, we will show you, how to use the avg() function. Hibernate supports multiple aggregate functions. When they are used in HQL queries, they return an aggregate value ( such as avg(...), sum(...), min(...), max(...) , count(*), count(...), count(distinct ...), count(all...) ) calculated from property values of all objects satisfying other query criteria. 

Following is a aggregate function (avg() function) with their respective syntax.

avg( [ distinct | all ] object.property):

The avg() function aggregates the average value of the given column. 

Table Name: insurance

ID insurance_name invested_amount investement_date
2 Life Insurance 25000 0000-00-00 00:00:00
1 Givan Dhara 20000  2007-07-30 17:29:05
3 Life Insurance   500 2005-10-15 00:00:00
4 Car Insurance    2500 2005-01-01 00:00:00
5 Dental Insurance 500 2004-01-01 00:00:00
Life Insurance 900 2003-01-01 00:00:00
Travel Insurance   2000  2005-02-02 00:00:00

Here is the java code to retrieve the average value of "invested_amount" column from insurance table:

package roseindia.tutorial.hibernate;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateHQLAvgFunction {

  /**
   * @Vinod kumar
   */
  public static void main(String[] args) {
    // TODO Auto-generated method stub

    Session sess = null;
    try {
      SessionFactory fact = new Configuration()
.configure().buildSessionFactory();
      sess = fact.openSession();
      String SQL_QUERY = "select avg
(investementAmount) from Insurance insurance"
;
      Query query = sess.createQuery(SQL_QUERY);
      List list = query.list();
      System.out.println("Average of 
Invested Amount: " 
+ list.get(0));
    }
    catch(Exception e){
      System.out.println(e.getMessage());
    }
  }

}

Download this Code.

Output:

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).

log4j:WARN Please initialize the log4j system properly.

Hibernate: select avg(insurance0_.invested_amount) as col_0_0_ from insurance insurance0_

Average of Invested Amount: 7342.8571

                         

» View all related tutorials
Related Tags: sql c hibernate api com class ant comparison import io property method sed const operators expression operator port criteria name

Leave your comment:

Name:

Email:

URL:

Title:

Comments:


Enter Code:

Audio Version
Reload Image
 

Note: Emails will not be visible or used in any way, and are not required. Please keep comments relevant. Any content deemed inappropriate or offensive may be edited and/or deleted.

No HTML code is allowed. Line breaks will be converted automatically. URLs will be auto-linked. Please use BBCode to format your text.

Add This Tutorial To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 

Current Comments

7 comments so far (
post your own) View All Comments Latest 10 Comments:

Change the query as

String SQL_QUERY = "select avg
(insurance.investementAmount) from Insurance insurance";

So that the above example will be executed.

Posted by Elay on Monday, 09.22.08 @ 15:49pm | #80598

Dear sir,

I m trying to run the above example but it is going into the exeption after the line


List list = query.list();

giving me the following err

No data type for node: org.hibernate.hql.ast.AggregateNode
[AGGREGATE] AggregateNode: 'min'
[ALIAS_REF] IdentNode: 'investementAmount' {alias=investementAmount, no from element}

Kinldy help me

Regards,
Abhishek

Posted by abhishek on Monday, 09.8.08 @ 16:06pm | #78009

Dear RoseIndia,

I m getting error in running the above sample

while debugging I m getting error on line

List list = query.list();

Kindly let me know is it the issue of compatibility of any installation.

Abhishek

Posted by abhishek on Monday, 09.8.08 @ 15:11pm | #77997

I want to retrieve max values of one column for all unique values of another column.Can any one help me to write the HQl query?

Posted by kisor on Tuesday, 02.26.08 @ 18:32pm | #50145

I want to learn how to display data in PDF from java program.

Posted by Manoj on Monday, 02.11.08 @ 17:23pm | #47904

Hi,

Could you please give me the mapping and config files for select query.


thanks
Revathi

Posted by revathi on Thursday, 12.27.07 @ 13:38pm | #43940

RoseIndian,

I want to learn how to retrieve data from xls sheet to the java program

Posted by praveena on Thursday, 12.6.07 @ 14:43pm | #41429

Training Courses
Tell A Friend
Your Friend Name
Website Designing Services
 
Web Designing Packages From $150!
 
Website Designing Company Web Hosting
 
Website Designing Quotation
 
Search Tutorials:

 

 
 

Home | JSP | EJB | JDBC | Java Servlets | WAP  | Free JSP Hosting  | Search Engine | News Archive | Jboss 3.0 tutorial | Free Linux CD's | Forum | Blogs

About Us | Advertising On RoseIndia.net  | Site Map

India News

Indian Software Development Company | iPhone Development Company in India | Flex Development Company in India | Java Training Delhi | Java Training at Noida |

Send your comments, Suggestions or Queries regarding this site at roseindia_net@yahoo.com.

Copyright © 2008. All rights reserved.