The jOOQ User Manual SQL building Column expressions Window functions Nested aggregate functions

previous : next

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.

The jOOQ User Manual SQL building Column expressions Window functions Nested aggregate functions

previous : next