Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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!