JOOQ select count distinct from group

Question

I am trying to count the number of records return by a group by select.

This stackoverflow question summarises the different approaches well:

Count number of records returned by group by

I would like to use the solution:

SELECT DISTINCT COUNT(*) OVER () AS TotalRecords
FROM table
GROUP BY column

How do I turn this into a JOOQ query?

This is how I would do a simple count query in JOOQ:

Record record = jooq.select( TABLE.COLUMN.count() ).from( TABLE).fetchOne();

return record.into( Long.class );

Is it possible to express the "DISTINCT COUNT(*) OVER () AS TotalRecords" in JOOQ syntax?

James

Answer 1

Write this:

// Assuming this static import
import static org.jooq.impl.DSL.*;

int totalRecords =
jooq.selectDistinct(count().over().as("TotalRecords"))
    .from(TABLE)
    .groupBy(TABLE.COLUMN)
    .fetchOneInto(int.class);

The methods you were missing were:

• DSLContext.selectDistinct()
• DSL.count()
• WindowOverStep.over() (WindowOverStep being a supertype of the type returned by DSL.count())