Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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 (...)
.
Feedback
Do you have any feedback about this page? We'd love to hear it!