The GROUP BY clause allows to divide the query results into groups.

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

Quick Example

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

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