Open In App

How to implement SQL GROUP BY in Java?

Improve
Improve
Like Article
Like
Save
Share
Report

Prerequisites: Java Collections, Java Array List, Comparator Interface in Java

Given a list object containing multiple records, our task is to perform SQL group by on the fields of the list in Java.

Input: A list containing employee records
       in random order. 
Output: List sorted first by department
        and then by age in each department

Approach:

  • Sort the list by using comparator for department and age respectively.
  • We then use the collections.sort() method first for the sorting.
  • We will sort the list by using the thenComparing() method of Comparator Interface. Using this method is very easy and it can be used to chain multiple comparators thus making the process easier.

Below is the implementation of the above approach:

Program:




// Java code to implement SQL GROUP BY
  
import java.util.*;
  
// Create the employee class
class Employee {
  
    int empid;
    String name;
    int age;
    String dept;
    int salary;
  
    public Employee(int empid, String name, int age,
                    String dept, int sal)
    {
        this.name = name;
        this.empid = empid;
        this.age = age;
        this.dept = dept;
        this.salary = sal;
    }
  
    public String getDept()
    {
        return this.dept;
    }
  
    public static void main(String[] args)
    {
  
        // create the employee list
        List<Employee> empData
            = new ArrayList<Employee>();
  
        // add values to list by
        // using the add() method
        // of the list interface
        empData.add(new Employee(1, "Ajay",
                                 25, "Technical", 35000));
        empData.add(new Employee(3, "Chandan",
                                 22, "Technical", 30000));
        empData.add(new Employee(4, "Arjun",
                                 30, "Management", 54000));
        empData.add(new Employee(2, "Arun",
                                 28, "Sales", 9000));
        empData.add(new Employee(8, "Anmol",
                                 40, "Sales", 15000));
        empData.add(new Employee(9, "Vivek",
                                 20, "Management", 8000));
        empData.add(new Employee(10, "Nikhil",
                                 27, "Sales", 7000));
        empData.add(new Employee(5, "Rahul",
                                 45, "Management", 60000));
        empData.add(new Employee(6, "Ganesh",
                                 32, "Sales", 35000));
        empData.add(new Employee(7, "Vishal",
                                 35, "Technical", 40000));
        empData.add(new Employee(11, "Anmol",
                                 23, "Sales", 15000));
        empData.add(new Employee(12, "Vivek",
                                 29, "Management", 8000));
        empData.add(new Employee(13, "Nikhil",
                                 30, "Technical", 7000));
  
        System.out.println("\n Employee list"
                           + " before sorting...\n");
        System.out.println(" ID    Name    Age"
                           + "    Department  Salary  \n");
  
        for (Employee e : empData) {
            System.out.format(" %2d  %7s   %d   %10s    %d \n",
                              e.empid, e.name, e.age, e.dept, e.salary);
        }
  
        // Sorting the list by department
        // and age by using the thenComparing() method
        Collections.sort(empData,
                         new DepartmentComparator()
                             .thenComparing(new AgeComparator()));
  
        System.out.println("\n Employee list after sorting...\n");
        System.out.println(" ID    Name    Age    Department  Salary  \n");
        for (Employee emp : empData) {
            System.out.format(" %2d  %7s   %d   %10s    %d \n",
                              emp.empid, emp.name, emp.age,
                              emp.dept, emp.salary);
        }
    }
}
  
class DepartmentComparator implements Comparator<Employee> {
  
    public int compare(Employee e1, Employee e2)
    {
        return e1.dept.compareTo(e2.dept);
    }
}
  
class AgeComparator implements Comparator<Employee> {
  
    public int compare(Employee e1, Employee e2)
    {
  
        if (e1.age == e2.age)
            return 0;
        else if (e1.age > e2.age)
            return 1;
        else
            return -1;
    }
}


Output:

Employee list before sorting...

 ID    Name    Age    Department  Salary  

  1     Ajay   25    Technical    35000 
  3  Chandan   22    Technical    30000 
  4    Arjun   30   Management    54000 
  2     Arun   28        Sales    9000 
  8    Anmol   40        Sales    15000 
  9    Vivek   20   Management    8000 
 10   Nikhil   27        Sales    7000 
  5    Rahul   45   Management    60000 
  6   Ganesh   32        Sales    35000 
  7   Vishal   35    Technical    40000 
 11    Anmol   23        Sales    15000 
 12    Vivek   29   Management    8000 
 13   Nikhil   30    Technical    7000 

 Employee list after sorting...

 ID    Name    Age    Department  Salary  

  9    Vivek   20   Management    8000 
 12    Vivek   29   Management    8000 
  4    Arjun   30   Management    54000 
  5    Rahul   45   Management    60000 
 11    Anmol   23        Sales    15000 
 10   Nikhil   27        Sales    7000 
  2     Arun   28        Sales    9000 
  6   Ganesh   32        Sales    35000 
  8    Anmol   40        Sales    15000 
  3  Chandan   22    Technical    30000 
  1     Ajay   25    Technical    35000 
 13   Nikhil   30    Technical    7000 
  7   Vishal   35    Technical    40000

Note: In the above output, the list is grouped by departments and the departments are further sorted by age of employees.



Last Updated : 28 Jun, 2019
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads