Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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, includingNULLvalues. -
COUNT(expression): This version counts the number of non-NULLexpression 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.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!