Multiple aggregate function with multiple group by using java streams

Question

I am not able to use multiple aggregate function with multiple group by attribute using java 8 stream apis. Yet I am able to use single aggregate function with multiple group by attribute using java stream but I have a requirement where I need multiple min or max or aggregate function to use modification on list.

I need the same result that can get it from SQL like below mentioned but I have an employee list and I have to do this with java only.

Note: Employee ID is not unique its duplicate in employee table in my example.

SELECT emp_id,
       emp_name,
       year_of_joining,
       gender,
       department,
       Min(salary),
       Min(age)
FROM   employee
GROUP  BY emp_id,
          emp_name,
          year_of_joining,
          gender,
          department 

Below is the list of Employee Object

List<Employee> employeeList = new ArrayList<Employee>();
         
employeeList.add(new Employee(111, "Jiya Brein", 32, "Female", "HR", 2011, 25000.0));
employeeList.add(new Employee(111, "Jiya Brein", 32, "Female", "HR", 2011, 21000.0));
employeeList.add(new Employee(111, "Jiya Brein", 32, "Female", "HR", 2011, 29000.0));
employeeList.add(new Employee(111, "Jiya Brein", 15, "Female", "HR", 2011, 25000.0));

Below is the Employee Object

class Employee
{
    int id;
     
    String name;
     
    int age;
     
    String gender;
     
    String department;
     
    int yearOfJoining;
     
    double salary;
     
    public Employee(int id, String name, int age, String gender, String department, int yearOfJoining, double salary) 
    {
        this.id = id;
        this.name = name;
        this.age = age;
        this.gender = gender;
        this.department = department;
        this.yearOfJoining = yearOfJoining;
        this.salary = salary;
    }
     
    public int getId() 
    {
        return id;
    }
     
    public String getName() 
    {
        return name;
    }
     
    public int getAge() 
    {
        return age;
    }
     
    public String getGender() 
    {
        return gender;
    }
     
    public String getDepartment() 
    {
        return department;
    }
     
    public int getYearOfJoining() 
    {
        return yearOfJoining;
    }
     
    public double getSalary() 
    {
        return salary;
    }
     
    @Override
    public String toString() 
    {
        return "Id : "+id
                +", Name : "+name
                +", age : "+age
                +", Gender : "+gender
                +", Department : "+department
                +", Year Of Joining : "+yearOfJoining
                +", Salary : "+salary;
    }
}

I have tried lot but I was not able to get the exact result with Employee Object. Any Help would be really appreciated. I don't know how to use multiple aggregate function with multiple group by statements so that in last I can get list of Employee object. I have tried to do this with below mentioned code but able to do with single aggregate. How can i do with multiple aggregate.

    Function<Employee, List<Object>> compositeKey = personRecord -> Arrays.<Object>asList(personRecord.getId(),
        personRecord.getName(), personRecord.getGender(), personRecord.getDepartment(),
        personRecord.getYearOfJoining());

    Map<List<Object>, Optional<Employee>> collect = employeeList.stream().collect(
        Collectors.groupingBy(compositeKey, Collectors.minBy(Comparator.comparingDouble(Employee::getSalary))));

Answer 1

Here is the solution.

Function<Employee, List<Object>> compositeKey = personRecord -> Arrays.<Object>asList(personRecord.getId(),
        personRecord.getName(), personRecord.getGender(), personRecord.getDepartment(),
        personRecord.getYearOfJoining());

 List<Employee> groupedEmployees = employeeList.stream().collect(Collectors.groupingBy(compositeKey)).values()
            .stream()
            .map(e -> new Employee(e.get(0).getId(), e.get(0).getName(),
                e.stream().mapToInt(Employee::getAge).min().orElse(0), e.get(0).getGender(), e.get(0).getDepartment(),
                e.get(0).getYearOfJoining(), e.stream().mapToDouble(Employee::getSalary).min().orElse(0)))
            .collect(Collectors.toList());

Answer 2

Here a dangerous solution

override Employee equals and HashCode methods like below(you can add null check if u need)

 @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Employee employee = (Employee) o;
        return id == employee.id && yearOfJoining == employee.yearOfJoining && name.equals(employee.name) && gender.equals(employee.gender) && department.equals(employee.department);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, name, gender, department, yearOfJoining);
    }

now u can group like that

final Map<Employee, List<Double>> groupedEmployees = employeeList.stream()
                .collect(Collectors.groupingBy(e -> e,
                        Collectors.collectingAndThen(Collectors.toList(), list -> {
                            final double minSalary = list.stream().mapToDouble(Employee::getSalary).min().orElse(0);
                            final double minAge = list.stream().mapToDouble(Employee::getAge).min().orElse(0);
                            return List.of(minSalary, minAge);
                        })));

for test add more data coz your data doesn't contain same group (do it before mapping :) )

employeeList.add(new Employee(111, "Jiya Brein", 32, "Female", "HR", 2011, 25000.0));
employeeList.add(new Employee(111, "Jiya Brein", 32, "Female", "HR", 2011, 21000.0));
employeeList.add(new Employee(111, "Jiya Brein", 32, "Female", "HR", 2011, 29000.0));
employeeList.add(new Employee(111, "Jiya Brein", 15, "Female", "HR", 2011, 25000.0));

groupedEmployees.forEach((k, v) -> System.out.println(k + " -> " + v));

Answer 3

This task is resolved by introducing separate objects/containers to store "groupBy" fields and "aggregated" fields. In the simplest form, raw lists could be used negore Java 14 but currently they can be replaced with record (in Java 14+).

ThenCollectors.toMap with merge function to implement actual aggregation should be used as shown below:

public record GroupBy(int id, String name, String gender, String department, int yearOfJoining) {}
public record Aggregate(int age, double salary) {}

List<Employee> groupedBy = employeeList.stream()
    .collect(Collectors.toMap(
        emp -> new GroupBy(emp.getId(), emp.getName(), emp.getGender(), 
            emp.getDepartment(), emp.getYearOfJoining()
        ), // grouped by fields
        emp -> new Aggregate(emp.getAge(), emp.getSalary()), // aggregated fields
        (ag1, ag2) -> new Aggregate(
            Math.min(ag1.age(), ag2.age()),
            Math.min(ag1.salary(), ag2.salary())
        )
        , LinkedHashMap::new // keep insertion order same as in the input
    )) // Map<GroupBy, Aggregate>
    .entrySet()
    .stream()
    .map(e -> new Employee(
        e.getKey().id(), e.getKey().name(), e.getValue().age(),
        e.getKey().gender(), e.getKey().department(), 
        e.getKey().yearOfJoining(), e.getValue().salary()
    ))    
    .collect(Collectors.toList());

---

Prior to Java 14, similar classes should be created and used, and for GroupBy class the methods hashCode and equals have to be implemented.

Assuming Lombok is used to generate all the boilerplate code, the same can be achieved:

@Accessors(fluent = true) // get rid of "get" prefixes
@Data
class GroupBy {
    private final int id;
    private final String name;
    private final String gender;
    private final String department;
    private final int yearOfJoining;
}

@Accessors(fluent = true)
@Data
@AllArgsConstructor
class Aggregate {
    private int age;
    private double salary;

    public Aggregate min(Aggregate other) {
        this.age = Math.min(this.age, other.age);
        this.salary = Math.min(this.salary, other.salary);
        return this;
    }
}

Here the merge function is implemented in Aggregate class itself.

List<Employee> groupedBy = employeeList.stream()
    .collect(Collectors.toMap(
        emp -> new GroupBy(emp.getId(), emp.getName(), emp.getGender(), 
            emp.getDepartment(), emp.getYearOfJoining()
        ), // grouped by fields
        emp -> new Aggregate(emp.getAge(), emp.getSalary()), // aggregated fields
        (ag1, ag2) -> Aggregate::min // use merge function
        , LinkedHashMap::new // keep insertion order same as in the input
    )) // Map<GroupBy, Aggregate>
    .entrySet()
    .stream()
    .map(e -> new Employee(
        e.getKey().id(), e.getKey().name(), e.getValue().age(),
        e.getKey().gender(), e.getKey().department(), 
        e.getKey().yearOfJoining(), e.getValue().salary()
    ))    
    .collect(Collectors.toList());