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.

The jOOQ Logo