|The jOOQ User Manual. Multiple Pages : SQL building : SQL Statements (DML) : The SELECT statement : The GROUP BY clause||previous : next|
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.
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();
Some databases support the SQL standard grouping functions and some extensions thereof. See the manual's section about grouping functions for more details.