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
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!