Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10

Nested aggregate functions

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Ordinary aggregate functions are evaluated before window functions, logically, in SQL's logical order of operations.

This means that when window functions are computed, the value of aggregate functions is already defined. This allows for some impressive looking expressions, like:

SELECT
  AUTHOR_ID,
  COUNT(*) AS BOOKS,
  SUM(COUNT(*)) OVER () AS ALL_BOOKS
FROM
  BOOK
GROUP BY AUTHOR_ID
create.select(
         BOOK.AUTHOR_ID,
         count().as("books"),
         sum(count()).over().as("all_books"))
     .from(BOOK)
     .groupBy(BOOK.AUTHOR_ID)
     .fetch();

Producing:

+-----------+-------+-----------+
| author_id | books | all_books |
+-----------+-------+-----------+
|         1 |     2 |         4 |
|         2 |     2 |         4 |
+-----------+-------+-----------+

In this example, the aggregate value of COUNT(*) can again be aggregated using SUM(COUNT(*)) OVER (...).

It is, however, not possible to nest window functions in window functions, just like aggregate functions cannot be nested in aggregate functions.

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo