|The jOOQ User Manual : SQL building : SQL Statements : The SELECT statement : GROUP BY clause||previous : next|
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(AUTHOR_ID);
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. The above example counts all books per author
jOOQ supports empty
GROUP BY () clauses as well. This will result in SELECT statements that return only one record.
SELECT COUNT(*) FROM BOOK GROUP BY ()
create.selectCount() .from(BOOK) .groupBy();
Some databases support the SQL standard grouping functions and some extensions thereof. See the manual's section about grouping functions for more details.