| The jOOQ User Manual. Multiple Pages : SQL building : Column expressions : Aggregate functions | previous : next |
New versions: Dev (3.14) | Latest (3.13) | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6
Aggregate functions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
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.
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.
