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, 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)
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!