Available in versions: Dev (3.22) | Latest (3.21) | 3.20 | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12
COUNT
Supported by ✅ 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 | +----+----------+----------------+
For historic reasons, this function returns ajava.lang.Integervalue. To support largerjava.lang.Longresult values, useDSL.countLarge()instead.
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.22. Support in older jOOQ versions may differ. Translate your own SQL on our website
References to this page
- The aggregate KEEP clause from Oracle
- The APPROX_COUNT_DISTINCT aggregate function
- Pattern based transformations: COUNT(*) scalar subquery comparison
- Pattern based transformations: COUNT(const)
- Pattern based transformations: COUNT(DISTINCT ..) with redundant arguments
- Pattern based transformations: COUNT(expr) scalar subquery comparison
Feedback
Do you have any feedback about this page? We'd love to hear it!