Hibernate Avg,Hibernate Avg Query,Hibernate Avg

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

insurance_name

invested_amount

investement_date

Life Insurance

25000

0000-00-00 00:00:00

Givan Dhara

20000

2007-07-30 17:29:05

Life Insurance

500

2005-10-15 00:00:00

Car Insurance

2500

2005-01-01 00:00:00

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());

    }

  }



}

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

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

View This Comment Separately

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

View This Comment Separately

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

View This Comment Separately

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

View This Comment Separately

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

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

View This Comment Separately

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

View This Comment Separately

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

View This Comment Separately