Aggregates

As we learned in SQL, aggregate functions can be used to distill a result set into in a single value.

These keywords still exist when using JPQL.

• COUNT: returns a long representing the number of elements selected.

  String query = "SELECT COUNT(f) FROM Film f WHERE f.id < 10";
  
  long count = em.createQuery(query, Long.class).getSingleResult();
  

  • We have traditionally used the .getResultList() method when aggregating a result set. Due to the fact that we know our query is going to return a single long value, we execute the statement with getSingleResult().

• SUM: returns the sum of the numeric values as a double.

  String query = "SELECT SUM(f.rentalRate) FROM Film f WHERE f.id < 10";
  
  double total = em.createQuery(query, Double.class).getSingleResult();
  

• AVG: returns the average of the numeric values returned.

  String query = "SELECT AVG(f.rentalRate) FROM Film f WHERE f.id < 10";
  
  double average = em.createQuery(query, Double.class).getSingleResult();
  

• MIN: returns the minimum of the returned values.

  String query = "SELECT MIN(f.rentalRate) FROM Film f WHERE f.id < 10";
  
  double minRate = em.createQuery(query, Double.class).getSingleResult();
  

• MAX: returns the maximum of the returned values.

  String query = "SELECT MAX(f.rentalRate) FROM Film f WHERE f.id < 10";
  
  double maxRate = em.createQuery(query, Double.class).getSingleResult();
  

If you would like to execute multiple aggregates in a single query, you can change the return type of the createQuery to an Object[].

String query = "SELECT MIN(f.rentalRate), MAX(f.rentalRate), AVG(f.rentalRate) FROM Film f WHERE f.id < 10";

Object[] results= em.createQuery(query, Object[].class).getSingleResult();

The returned Object[] would have the min stored in index 0, max in index 1 and avg stored in index 2.

Drill

1. Open your IntermediateJPQLClient class.
2. In the main method write a query to calculate the total number of Films in the database.

---

Prev -- Up -- Next