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

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.20, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo