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, includingNULL
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
Feedback
Do you have any feedback about this page? We'd love to hear it!