How to get the count of records based on selectCase in JPA

Question

I want to count the number for records that are satisfying the conditions

Example:-

There are 400 records in table and table consist of employee_name and employee_salary and I want to find the count of employees who are having salary less than 20K and count of employees who are having salary greater than 20K. I have tried few things like we can achieve using criteriaBuilder.selectCase() but i am not able to find exact solution.

Thanks for the help

Answer 1

It may be more convenient to use native query in the JPA repository:

@Repository
public interface EmployeeRepository extends CrudRepository<Employee, Integer> {

    @Query(nativeQuery = true,
           value = "SELECT (CASE WHEN e.salary < :minLimit THEN 'Low' ELSE 'High' END) isLow, COUNT(*) "
                   + "FROM employee e  GROUP BY isLow")
    List<Object[]> calcStats(@Param("minLimit") Integer minLimit);
}

Then this method can be invoked via autowired repository:

employeeRepository.calcStats(20_000)
                  .forEach(e -> System.out.printf("%s - %d%n", e[0], e[1]));

Update

If it's required to return 0 always, the query needs to be updated:

SELECT salary_types.salary_type, COALESCE(sum(stats.cc), 0) cc
FROM (
    SELECT CASE WHEN salary < :minLimit THEN 'low' ELSE 'high' END isLow, COUNT(*) cc 
    FROM employee e
    GROUP BY isLow) stats
RIGHT OUTER JOIN (
    SELECT salary_type
    FROM (VALUES ('high'), ('low'))
    salary_types(salary_type_type)
) salary_types ON stats.isLow = salary_types.salary_type
GROUP BY salary_types.salary_type;

For PostgreSQL it is possible to join on this shorter variant

RIGHT JOIN (SELECT UNNEST(ARRAY['high', 'low']) salary_type) salary_types