CODEWITHSUNDEEP
javamysqldistinctaggregate-functionsjooq
genechu
genechu

Reputation: 97

how Jooq distinct or countDistinct more than one column

I found that the distinct function in the Factory class only accepts one parameter

public static AggregateFunction<Integer> countDistinct(Field<?> field);

and there is no function like distinct(Field... fields)

I want to to express a SQL statement like this:

select c1, count(distinct c2,c3) from t1 group by c1;

PS: there is a non-dsl api: SelectQuery.setDistinct(true), but it will "distinct" all columns, which is not what I want.

Upvotes: 1

Views: 3352

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221105

That is interesting. To my knowledge, this should not be possible in SQL. The SQL:2008 standard specifies

10.9 <aggregate function>

Format
<aggregate function> ::=
      COUNT <left paren> <asterisk> <right paren> [ <filter clause> ]
    | <general set function> [ <filter clause> ]

<general set function> ::=
  <set function type> <left paren> [ <set quantifier> ]
    <value expression> <right paren>

There is no room for comma-separated argument lists, with or without the DISTINCT set quantifier. This is implemented correctly in Oracle:

enter image description here

Yet, MySQL seems to allow for this non-standard "convenient syntax":

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of rows with different non-NULL expr values.

(taken from http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_count).

To answer your question, this is currently not possible in jOOQ, although I filed #1728 for future support. Currently, you can work around this by writing your own multi-argument aggregate function support:

Field<Integer> myCount = Factory.field(
  "count(distinct {0}, {1})", Integer.class, c2, c3);

See the relevant Javadoc for the Factory.field(String, Class<T>, QueryPart...) method for details

This is now implemented in jOOQ 2.6:

You can write

Field<Integer> count = Factory.countDistinct(c2, c3);

See the Javadoc for the Factory.countDistinct(Field...) method, for details

Upvotes: 5

Related Questions