Group by clause is used to return the aggregate values by grouping on returned component. HQL supports Group By Clause. In our example we will calculate the sum of invested amount in each insurance type. Here is the java code for calculating the invested amount insurance wise:

import org.hibernate.Session;
import org.hibernate.*;
import org.hibernate.cfg.*;
import java.util.*;

public class HQLGroupByExample {
  public static void main(String[] args) {
    Session session = null;
    try {
      // This step will read hibernate.cfg.xml and prepare hibernate for
      // use
      SessionFactory sessionFactory = new Configuration().configure()
          .buildSessionFactory();
      session = sessionFactory.openSession();
      //Group By Clause Example
      String SQL_QUERY = "select sum(insurance.investementAmount),insurance.insuranceName "
          + "from Insurance insurance group by insurance.insuranceName";
      Query query = session.createQuery(SQL_QUERY);
      for (Iterator it = query.iterate(); it.hasNext();) {
        Object[] row = (Object[]) it.next();
        System.out.println("Invested Amount: " + row[0]);
        System.out.println("Insurance Name: " + row[1]);
      }
      session.close();
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
    }
  }
}

To run the example select Run-> Run As -> Java Application from the menu bar. Following out is displayed in the Eclipse console:

Hibernate: select sum(insurance0_.invested_amount) as col_0_0_, insurance0_.insurance_name as col_1_0_ from insurance insurance0_ group by insurance0_.insurance_name
Invested Amount: 3500

Insurance Name: Car Insurance

Invested Amount: 500

Insurance Name: Dental Insurance

Invested Amount: 1550

Insurance Name: Home Insurance

Invested Amount: 1500

Insurance Name: Life Insurance

Invested Amount: 1600

Insurance Name: Medical Insurance

Invested Amount: 1680

Insurance Name: Motorcycle Insurance

Invested Amount: 2600

Insurance Name: Travel Insurance

1. What is Hibernate?

2. Hibernate Architecture

3. Writing First Hibernate Code

4. Understanding Hibernate O/R Mapping

5. Understanding Hibernate <generator> element

6. Using Hibernate <generator> to generate id incrementally

7. Hibernate Query Language

8. Preparing table for HQL Examples

9. Writing ORM for Insurance table

10. HQL from clause Example

11. Hibernate Select Clause

12. HQL Where Clause Example

13. HQL Group By Clause Example

14. HQL Order By Example

15. Hibernate Criteria Query Example

16. Criteria Query Examples

17. Hibernate Native SQL Example

HQL Group By Clause Example