create a histogram using jooq

Question

I want to create a histogram of the values in a single column of a table, and I already know all the possible values for that col

so, in pseudo-sql, I want to do something like this:

select
count(my_col where my_col == "val_1") as "count_for_val_1",
count(my_col where my_col == "val_2") as "count_for_val_2",
etc

one caveat is that I want to use this style of query building in joog, as it makes conditional construction easier:

SelectQuery query = context.selectQuery();
query.addSelect(...)
query.addFrom(...)
etc

update: ok this seems to work:

SelectQuery query = context.selectQuery();
query.addSelect(my_table.my_col.decode("val_1", "any string here?").count().as("count_for_val_1"));
query.addSelect(my_table.my_col.decode("val_2", "any string here?").count().as("count_for_val_2"));
etc

now, is best way to do this in JOOQ? there are so many...

Answer 1

You could use jOOQ's filterWhere() function, which can be used with all aggregate functions. I.e.

DSL.using(configuration)
   .select(
       count().filterWhere(MY_COL.eq("val_1")).as("count_for_val_1"),
       count().filterWhere(MY_COL.eq("val_2")).as("count_for_val_2"),
       ...
   )
   .from(...)

The SQL standard specifies the AGGREGATE(...) FILTER (WHERE ...) syntax, which is natively supported in PostgreSQL:

SELECT
  count(*) FILTER (WHERE my_col = 'val_1') AS "count_for_val_1",
  count(*) FILTER (WHERE my_col = 'val_2') AS "count_for_val_2",
  ...
FROM ...

In all other databases, jOOQ will generate the following instead

SELECT
  count(CASE WHEN my_col = 'val_1' THEN 1 END) AS "count_for_val_1",
  count(CASE WHEN my_col = 'val_2' THEN 1 END) AS "count_for_val_2",
  ...
FROM ...