Close

JPQL GROUP BY and HAVING Clauses

[Updated: May 16, 2018, Created: May 16, 2018]

The GROUP BY clause allows to group values according to a set of properties.

Optional HAVING clause can be used with GROUP BY to filter the groups.

Examples

Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private String dept;
  private long salary;
    .............
}

Using GROUP BY - HAVING

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeeCountGroupByDept();
          findEmployeeAvgSalariesGroupByDept();
          findEmployeeCountGroupBySalary();
          findEmployeeMaxSalariesGroupBySelectedDept();

      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", "IT", 3000);
      Employee employee2 = Employee.create("Rose", "Admin", 2000);
      Employee employee3 = Employee.create("Denise", "Admin", 4000);
      Employee employee4 = Employee.create("Mike", "IT", 3500);
      Employee employee5 = Employee.create("Linda", "Sales", 2000);
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.persist(employee5);
      em.getTransaction().commit();
      em.close();
  }

  private static void findEmployeeCountGroupByDept() {
      System.out.println("-- Employee count group by dept --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT e.dept, COUNT(e) FROM Employee e GROUP BY e.dept");
      List<Object[]> resultList = query.getResultList();
      resultList.forEach(r -> System.out.println(Arrays.toString(r)));
      em.close();
  }

  private static void findEmployeeAvgSalariesGroupByDept() {
      System.out.println("-- Employees avg salary group by dept --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT e.dept, AVG(e.salary) FROM Employee e GROUP BY e.dept");
      List<Object[]> resultList = query.getResultList();
      resultList.forEach(r -> System.out.println(Arrays.toString(r)));
      em.close();
  }

  private static void findEmployeeCountGroupBySalary() {
      System.out.println("-- Employee count group by salary --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT e.salary, COUNT(e) FROM Employee e GROUP BY e.salary");
      List<Object[]> resultList = query.getResultList();
      resultList.forEach(r -> System.out.println(Arrays.toString(r)));
      em.close();
  }

  private static void findEmployeeMaxSalariesGroupBySelectedDept() {
      System.out.println("-- Employees max salary group by dept - only in IT and Admin dept --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT e.dept, MAX(e.salary) FROM Employee e GROUP BY e.dept HAVING e.dept IN ('IT', 'Admin')");
      List<Object[]> resultList = query.getResultList();
      resultList.forEach(r -> System.out.println(Arrays.toString(r)));
      em.close();
  }
}
-- Employee count group by dept --
[IT, 2]
[Sales, 1]
[Admin, 2]
-- Employees avg salary group by dept --
[IT, 3250.0]
[Sales, 2000.0]
[Admin, 3000.0]
-- Employee count group by salary --
[2000, 2]
[4000, 1]
[3500, 1]
[3000, 1]
-- Employees max salary group by dept - only in IT and Admin dept --
[IT, 3500]
[Admin, 4000]

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
    Implements javax.persistence:javax.persistence-api version 2.1
  • JDK 1.8
  • Maven 3.3.9

JPQL GROUP BY and HAVING Clauses Select All Download
  • jpql-group-by-having-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also