Spring Data JPA, use GROUP BY to return a list of rows grouped by a column

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

I am trying to get the result of the following query using Spring Data JPA:

SELECT e.dept, e
FROM Employee e
GROUP BY e.dept
HAVING e.dept IN ('IT', 'Admin')

We cannot have a map response using default Spring Data JPA implementations and hence the above query cannot be directly used in the repository layer.

How can I get that result using a criteria with a paginated response?

The data size is expected to be large and grouping the data in Java using stream might not be an option.

The query that i gave itself is not directly possible in sql I guess . I want to know if there is any way to achieve the same result.

SELECT e.dept, e
FROM Employee e
GROUP BY e.dept
HAVING e.dept IN ('IT', 'Admin')

is not valid SQL or JPQL. In SQL, when using GROUP BY e.dept, you cannot select the full entity e because it contains multiple columns (like id, name, salary) that are not grouped or aggregated.

You can achieve grouped results with pagination by:

• Selecting aggregated data (like dept and COUNT(*))

• Using Spring Data JPA interface-based projections for clean mapping

• Applying Pageable for paginated response

Here’s how you can do it:

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

     @Query("SELECT e.dept AS dept, COUNT(e) AS employeeCount " +
             "FROM Employee e " +
             "GROUP BY e.dept " +
             "HAVING e.dept IN :depts")
     Page<DeptEmployeeCount> countEmployeesByDept(@Param("depts") List<String> depts, Pageable pageable);

}

public  interface DeptEmployeeCount {
    String getDept();
    Long getEmployeeCount();
}

1. You cannot group by dept and select the full entity e directly. That’s invalid SQL.

2. Instead, select aggregated fields (like e.dept and COUNT(e)).

3. Spring Data JPA supports interface-based projections (DeptEmployeeCount) to map partial query results cleanly.

4. Pagination works by returning Page<DeptEmployeeCount>, which includes pagination metadata (totalPages, totalElements, etc.).

I got proper response as you can see in below screen shot:

generated hibernate query

References:

- https://docs.spring.io/spring-data/jpa/reference/repositories/projections.html
- https://www.baeldung.com/jpa-queries-custom-result-with-aggregation-functions