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
GROUP BY clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
GROUP BY can be used to create unique groups of data, to form aggregations, to remove duplicates and for other reasons. It will transform your previously defined set of table expressions, and return only one record per unique group as specified in this clause. For instance, you can group books by BOOK.AUTHOR_ID:
SELECT AUTHOR_ID, COUNT(*) FROM BOOK GROUP BY AUTHOR_ID
create.select(BOOK.AUTHOR_ID, count())
.from(BOOK)
.groupBy(BOOK.AUTHOR_ID)
.fetch();
The above example counts all books per author.
Note, as defined in the SQL standard, when grouping, you may no longer project any columns that are not a formal part of the GROUP BY clause, or aggregate functions.
Empty GROUP BY clauses
jOOQ supports empty GROUP BY () clause as well. This will result in SELECT statements that return only one record.
SELECT COUNT(*) FROM BOOK GROUP BY ()
create.selectCount()
.from(BOOK)
.groupBy()
.fetch();
ROLLUP(), CUBE() and GROUPING SETS()
Some databases support the SQL standard grouping functions and some extensions thereof. See the manual's section about grouping functions for more details.
Feedback
Do you have any feedback about this page? We'd love to hear it!