New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.3

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!

The jOOQ Logo