How to write nested query in jooq with SUM operation from the subquery output?

Question

Let's say I want to find out the sum of one of the table from the nested subquery. Let's take the subquery given below. How to calculate the status count using DSL.sum(when(condition))

Table<Record4<Integer, Integer, String, String>> orgWiseCountTable = dsl.select(
                         field("master_id", Integer.class).as(masterId),
                         field("transaction_id", Integer.class).as(transactionId),
                         field("api_status", String.class).as(status),
                         field("Organisations.organisation_name",String.class).as(organisationName)
            )
                    .from(TestTable)

                    .leftJoin(Organisations)

                    .on(TestTable.organsiationId.eq(Organisations.organsiationId)

                    .where(condition)

                    .groupBy(TestTable.master_id).asTable("orgWiseCount");

Using the above Table record how can we calculate the sum of api_status depending on whether or not the value is COMPLETED or not.

Trying the below approach but it isn't working.

transactions = dsl.select(
                    orgWiseCountTable.field(organisationName).as("OrganisationName"),
                    orgWiseCountTable.field("SUM(status = 'COMPLETED')").as(
                                    "SuccessCount"),
                    orgWiseCountTable.field("SUM(kyc_api_transaction_master.api_status <> 'COMPLETED')").as(
                                    "FailureCount"),
                    orgWiseCountTable.field(totalCount).as("TotalCount")
                    )

                    .from(orgWiseCountTable)
                    .fetch()
                    .into(Count.class);

Getting Null pointer exception while calculating sum. How can this be fixed?

Answer 1

Using derived tables

As I answered to your follow-up question, I don't think you really need the derived table. You'll simplify your life greatly, if you just remove it.

Dereferencing columns from a derived table

If your actual query is more complex than the one you've presented here, you'll have to dereference columns from your derived table like this:

// Dereference first:
Field<String> status = orgWiseCountTable.field("status", String.class);

// Alternatively, dereference using generaetd code for added convenience:
Field<String> status = orgWiseCountTable.field(THE_TABLE.STATUS);

// Then use in expressions:
DSL.count().filterWhere(status.eq("Completed")).as("SuccessCount");

Aggregate filter clause

Note, I'm using FILTER instead, which is a standard SQL feature. This will be a bit more convenient to write in jOOQ, than using the MySQL specific approach of mapping booleans to integers in order to sum them. A.eq(B) is a Condition in jOOQ, which extends Field<Boolean>, and you cannot really sum booleans.

Code generation

This isn't necessary relevant to your question, but I highly recommend you start using jOOQ's code generator. A lot of features will be unlocked by that, and your queries will be much simpler to write.