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

COUNT

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The COUNT() aggregate function comes in two flavours:

  • COUNT(*): This version counts the number of tuples in a group, regardless of any contents, including NULL values.
  • COUNT(expression): This version counts the number of non-NULL expression evaluations per group.

The second version can be used to emulate the FILTER clause as the argument expression effectively filters out NULL values. Alternatively, in the case of a LEFT JOIN, the outer joined rows can be counted using an expression on the primary key, because COUNT(*) always produces at least one row.

SELECT
  AUTHOR.ID,
  count(*),
  count(BOOK.ID)
FROM AUTHOR
LEFT JOIN BOOK
ON BOOK.AUTHOR_ID = AUTHOR.ID
create.select(
         AUTHOR.ID,
         count(),
         count(BOOK.ID))
      .from(AUTHOR)
      .leftJoin(BOOK)
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))

Producing (assuming the presence of an author with ID = 3, but without books):

+----+----------+----------------+
| ID | count(*) | count(BOOK.ID) |
+----+----------+----------------+
|  1 |        2 |              2 |
|  2 |        2 |              2 |
|  3 |        1 |              0 |
+----+----------+----------------+

Dialect support

This example using jOOQ:

count(BOOK.ID)

Translates to the following dialect specific expressions:

-- All dialects
count(BOOK.ID)

(These are currently generated with jOOQ 3.15, see #10141)

The jOOQ Logo