Aggregate functions

Aggregate functions work just like functions, even if they have a slightly different semantics. Here are some example aggregate functions from the DSL:

// Every-day, SQL standard aggregate functions
AggregateFunction<Integer>    count();
AggregateFunction<Integer>    count(Field<?> field);
AggregateFunction<T>          max  (Field<T> field);
AggregateFunction<T>          min  (Field<T> field);
AggregateFunction<BigDecimal> sum  (Field<? extends Number> field);
AggregateFunction<BigDecimal> avg  (Field<? extends Number> field);

// DISTINCT keyword in aggregate functions
AggregateFunction<Integer>    countDistinct(Field<?> field);
AggregateFunction<T>          maxDistinct  (Field<T> field);
AggregateFunction<T>          minDistinct  (Field<T> field);
AggregateFunction<BigDecimal> sumDistinct  (Field<? extends Number> field);
AggregateFunction<BigDecimal> avgDistinct  (Field<? extends Number> field);

// String aggregate functions
AggregateFunction<String> groupConcat        (Field<?> field);
AggregateFunction<String> groupConcatDistinct(Field<?> field);
OrderedAggregateFunction<String> listAgg(Field<?> field);
OrderedAggregateFunction<String> listAgg(Field<?> field, String separator);

// Statistical functions
AggregateFunction<BigDecimal> median    (Field<? extends Number> field);
AggregateFunction<BigDecimal> stddevPop (Field<? extends Number> field);
AggregateFunction<BigDecimal> stddevSamp(Field<? extends Number> field);
AggregateFunction<BigDecimal> varPop    (Field<? extends Number> field);
AggregateFunction<BigDecimal> varSamp   (Field<? extends Number> field);

// Linear regression functions
AggregateFunction<BigDecimal> regrAvgX     (Field<? extends Number> y, Field<? extends Number> x);
AggregateFunction<BigDecimal> regrAvgY     (Field<? extends Number> y, Field<? extends Number> x);
AggregateFunction<BigDecimal> regrCount    (Field<? extends Number> y, Field<? extends Number> x);
AggregateFunction<BigDecimal> regrIntercept(Field<? extends Number> y, Field<? extends Number> x);
AggregateFunction<BigDecimal> regrR2       (Field<? extends Number> y, Field<? extends Number> x);
AggregateFunction<BigDecimal> regrSlope    (Field<? extends Number> y, Field<? extends Number> x);
AggregateFunction<BigDecimal> regrSXX      (Field<? extends Number> y, Field<? extends Number> x);
AggregateFunction<BigDecimal> regrSXY      (Field<? extends Number> y, Field<? extends Number> x);
AggregateFunction<BigDecimal> regrSYY      (Field<? extends Number> y, Field<? extends Number> x);

Here's an example, counting the number of books any author has written:

SELECT AUTHOR_ID, COUNT(*)
FROM BOOK
GROUP BY AUTHOR_ID

create.select(BOOK.AUTHOR_ID, count())
      .from(BOOK)
      .groupBy(BOOK.AUTHOR_ID)
      .fetch();

Aggregate functions have strong limitations about when they may be used and when not. For instance, you can use aggregate functions in scalar queries. Typically, this means you only select aggregate functions, no regular columns or other column expressions. Another use case is to use them along with a GROUP BY clause as seen in the previous example. Note, that jOOQ does not check whether your using of aggregate functions is correct according to the SQL standards, or according to your database's behaviour.

Filtered aggregate functions

The SQL standard specifies an optional FILTER clause, that can be appended to all aggregate functions. This is very useful to implement "pivot" tables, such as the following:

SELECT
  count(*),
  count(*) FILTER (WHERE TITLE LIKE 'A%')
FROM BOOK

create.select(
         count(),
         count().filterWhere(BOOK.TITLE.like("A%")))
      .from(BOOK)

It is usually a good idea to calculate multiple aggregate functions in a single query, if this is possible.

Only few databases (e.g. HSQLDB, PostgreSQL) implement native support for the FILTER clause. In all other databases, jOOQ emulates the clause using a CASE expression:

SELECT
  count(*),
  count(CASE WHEN TITLE LIKE 'A%' THEN 1 END)
FROM BOOK

Aggregate functions exclude NULL values from aggregation, so the above query is equivalent to the one using FILTER.

Ordered-set aggregate functions

Oracle and some other databases support "ordered-set aggregate functions". This means you can provide an ORDER BY clause to an aggregate function, which will be taken into consideration when aggregating. The best example for this is Oracle's LISTAGG() (also known as GROUP_CONCAT in other SQL dialects). The following query groups by authors and concatenates their books' titles

SELECT   LISTAGG(TITLE, ', ')
         WITHIN GROUP (ORDER BY TITLE)
FROM     BOOK
GROUP BY AUTHOR_ID

create.select(listAgg(BOOK.TITLE, ", ")
      .withinGroupOrderBy(BOOK.TITLE))
      .from(BOOK)
      .groupBy(BOOK.AUTHOR_ID)
      .fetch();

The above query might yield:

+---------------------+
| LISTAGG             |
+---------------------+
| 1984, Animal Farm   |
| O Alquimista, Brida |
+---------------------+

FIRST and LAST: Oracle's "ranked" aggregate functions

Oracle allows for restricting aggregate functions using the KEEP() clause, which is supported by jOOQ. In Oracle, some aggregate functions (MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEV) can be restricted by this clause, hence org.jooq.AggregateFunction also allows for specifying it. Here are a couple of examples using this clause:

SUM(BOOK.AMOUNT_SOLD)
  KEEP(DENSE_RANK FIRST ORDER BY BOOK.AUTHOR_ID)

sum(BOOK.AMOUNT_SOLD)
  .keepDenseRankFirstOrderBy(BOOK.AUTHOR_ID)

User-defined aggregate functions

jOOQ also supports using your own user-defined aggregate functions. See the manual's section about user-defined aggregate functions for more details.

Window functions / analytical functions

In those databases that support window functions, jOOQ's org.jooq.AggregateFunction can be transformed into a window function / analytical function by calling over() on it. See the manual's section about window functions for more details.

The jOOQ User Manual. Multiple Pages : SQL building : Column expressions : Aggregate functions

previous : next