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)
Generated with jOOQ 3.20. Translate your own SQL on our website

References to this page

Feedback

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

The jOOQ Logo