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

All versions: 3.11 | 3.10 | **3.9** | 3.8 | 3.7 | Development versions: 3.12 | Unsupported versions: 3.6 | 3.5 | 3.4 | 3.3 | 3.2 | 2.6

# 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.