JPQL supports AVG, COUNT, MAX, MIN, and SUM aggregate functions in SELECT statements.
AVG
COUNT
MAX
MIN
SUM
@Entity public class Employee { @Id @GeneratedValue private long id; private String name; private long salary; ............. }
public class ExampleMain { private static EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("example-unit"); public static void main(String[] args) { try { persistEmployees(); findEmployeeCount(); findEmployeeAvgSalary(); findEmployeeMaxSalary(); findEmployeeMinSalary(); findEmployeeSalariesSum(); } finally { entityManagerFactory.close(); } } public static void persistEmployees() { Employee employee1 = Employee.create("Diana", 3000); Employee employee2 = Employee.create("Rose", 4000); Employee employee3 = Employee.create("Denise", 1500); Employee employee4 = Employee.create("Mike", 2000); EntityManager em = entityManagerFactory.createEntityManager(); em.getTransaction().begin(); em.persist(employee1); em.persist(employee2); em.persist(employee3); em.persist(employee4); em.getTransaction().commit(); em.close(); } private static void findEmployeeCount() { System.out.println("-- Employee COUNT --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT COUNT(e) FROM Employee e"); Long result = (Long) query.getSingleResult(); System.out.println(result); em.close(); } private static void findEmployeeAvgSalary() { System.out.println("-- Employee AVG Salary --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT AVG(e.salary) FROM Employee e"); Double result = (Double) query.getSingleResult(); System.out.println(result); em.close(); } private static void findEmployeeMaxSalary() { System.out.println("-- Employee MAX Salary --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT MAX(e.salary) FROM Employee e"); Long result = (Long) query.getSingleResult();//salary of type long System.out.println(result); em.close(); } private static void findEmployeeMinSalary() { System.out.println("-- Employee MIN Salary --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT MIN(e.salary) FROM Employee e"); Long result = (Long) query.getSingleResult();//salary of type long System.out.println(result); em.close(); } private static void findEmployeeSalariesSum() { System.out.println("-- Employee SUM Salary --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT SUM(e.salary) FROM Employee e"); Long result = (Long) query.getSingleResult(); System.out.println(result); em.close(); } }
-- Employee COUNT --4-- Employee AVG Salary --2625.0-- Employee MAX Salary --4000-- Employee MIN Salary --1500-- Employee SUM Salary --10500
Dependencies and Technologies Used: