Spring data jpa count group by

1. Overview

In this article, we will learn to count the rows based on the group by clause in Spring Data JPA. The GROUP BY clause is used to group rows that have the same values in one or more columns.

2. Spring Data JPA count group by

For simple use-cases, you can easily write a derived query method inside your Repository class. However, these derived queries such as findAllOrderByNameAsc don’t support Group By keyword. For the same reason, we choose @Query to write dynamic queries with Group By clause.

See this article for using order by in your application.

2.1. Entity class

First, let’s create an entity class that represents the Customer table in the database. We specified the primary key of the entity class by annotating the field id with @Id.

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Customer {

    @Id
    private String id;
    private String name;
    private String customer_type;
}

Here, we have annotated the Entity class with annotations @AllArgsConstructor and @Data from the Lombok library to generate constructors and getter/setter methods.

However, you can also manually create constructors and getters/setters.

3. JpaRepository class

Now, let’s create a repository class that implements the JpaRepository interface. The annotation @Repository is a specialization of @Component annotation which is used to show that the implementing class provides the mechanism for storage, retrieval, update, delete and search operation on DB objects.

The JpaRepository extends PagingAndSortingRepository which in turn extends CrudRepository. Because of this inheritance, JpaRepository will have all the functions of CrudRepository and PagingAndSortingRepository.

@Repository
public interface CustomerRepository extends JpaRepository<Customer, String> 

4.1. Spring JPA count grouped by some field

The following CustomerRepository class contains a dynamic query to calculate total customers grouped by the customer types. Each Object[] contains customer_type and its corresponding count. If you want to get entity or POJO objects instead of Object[], see this article to map the result set.

@Repository
public interface CustomerRepository extends JpaRepository<Customer, String> {

    List<Customer> findAllByOrderByNameDesc();

    @Query(value = "SELECT c.customer_type, COUNT(c.customer_type) FROM Customer AS c GROUP BY c.customer_type")
    List<Object[]> countCustomersByType();
}

Consider we have the following customers in the table.

The below DAO code retrieves the data from the CustomerRepository and returns the result back to the controller.

public List<Object[]> countCustomersByType() {
        return customerRepository.countCustomersByType();
}

If you execute the above code, it produces the following results. As you can see, it returns the count of customers grouped by the customer type.

[["Angry",1],["Loyal",1],["New",2]]

4. Conclusion

To sum up, we have learned to count the rows grouped by some field in Spring Data JPA. You can find the code samples of this article in our GitHub repository.