Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Distinctness
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A useful thing to do when aggregating data is to remove duplicate input first, prior to aggregation. A few aggregate functions support a DISTINCT keyword for that purpose. For example, we can query
SELECT count(AUTHOR_ID), count(DISTINCT AUTHOR_ID), group_concat(AUTHOR_ID), group_concat(DISTINCT AUTHOR_ID) FROM BOOK
create.select(
count(BOOK.AUTHOR_ID),
countDistinct(BOOK.AUTHOR_ID),
groupConcat(BOOK.AUTHOR_ID),
groupConcatDistinct(BOOK.AUTHOR_ID))
.from(BOOK).fetch();
Producing:
+-------+----------------+--------------+-----------------------+ | count | count_distinct | group_concat | group_concat_distinct | +-------+----------------+--------------+-----------------------+ | 4 | 2 | 1, 1, 2, 2 | 1, 2 | +-------+----------------+--------------+-----------------------+
If DISTINCT is available through the jOOQ API, it is always appended to the aggregate function name, such as count() and countDistinct(). sum() and sumDistinct(), etc.
Feedback
Do you have any feedback about this page? We'd love to hear it!