How to write nested sql query in jooq with SUM operation in SELECT

Question

I have a nested sql query and I want to write that query in jooq.

select organisation_name, sum(api_status = 'COMPLETED') as success_count, sum(api_status <> 'COMPLETED') as failure_count, count(distinct transaction_id) as 'total'
from (
select test_master_table.master_id, test_master_table.transaction_id, test_master_table.api_status, organsiations_table.organisation_name
from test_master_table 
left join organsiations_table on test_master_table.organisation_id = organsiations_table.organisation_id 
left join upload_table on test_master_table.master_id = upload_table.master_id
where (test_master_table.organisation_id = '1' AND (created_date >= current_timestamp()))
group by test_master_table.master_id) as test;

How can this be done in JOOQ?

Here is the sample SQL query which can be used to replicate the desired output.If executed the given query below with the table data attached, the nested query provide a different result than the one suggested(query for which has been attached as well).

CREATE TABLE test_master_table (
    master_id INTEGER PRIMARY KEY,
    transaction_id TEXT NOT NULL,
    api_status TEXT,
    organsiation_id INTEGER Not NULL
  );

CREATE TABLE organisation_table (
    organsiation_id INTEGER not NULL,
    organisation_name TEXT not null
);

CREATE TABLE upload_table (
    master_id INTEGER NOT NULL,
    statement_id TEXT PRIMARY KEY,
    file_status TEXT,
    type TEXT
);

INSERT INTO test_master_table values (1, 'txn-1', 'ERROR', 1);
INSERT INTO test_master_table values(2, 'txn-2', 'ERROR', 1);
INSERT INTO test_master_table values (3, 'txn-3', 'COMPLETED', 1);
INSERT INTO test_master_table values (4, 'txn-4', 'COMPLETED', 1);

INSERT INTO organisation_table values (1,'org-1');
INSERT INTO organisation_table values (2,'org-2');
INSERT INTO organisation_table values (3,'org-3');

INSERT INTO upload_table values (1, 'stmt-1', 'COMPLETED', 'type-1');
INSERT INTO upload_table values (1, 'stmt-2', 'COMPLETED', 'type-1');
INSERT INTO upload_table values (1, 'stmt-3', 'COMPLETED', 'type-1');
INSERT INTO upload_table values (2, 'stmt-4', 'COMPLETED', 'type-1');
INSERT INTO upload_table values (2, 'stmt-5', 'COMPLETED', 'type-1');
INSERT INTO upload_table values (2, 'stmt-6', 'COMPLETED', 'type-1');


-- Query to get the desired output

select organisation_name, sum(api_status = 'COMPLETED') as successCount, sum(api_status <> 'COMPLETED') as failureCount, count(distinct master_id)
from (
select test_master_table.master_id, test_master_table.transaction_id, test_master_table.api_status, organisation_table.organisation_name
from test_master_table
left join organisation_table on test_master_table.organsiation_id = organisation_table.organsiation_id
left join upload_table on test_master_table.master_id = upload_table.master_id
where organisation_table.organsiation_id = '1' and type = 'type-1'
group by test_master_table.master_id
) as test;


-- Query suggested (does not reproduce the desired result) 

/*select organisation_table.organisation_name, sum(api_status = 'COMPLETED') as successCount, sum(api_status <> 'COMPLETED') as failureCount, count(distinct test_master_table.master_id)
from test_master_table
left join organisation_table on test_master_table.organsiation_id = organisation_table.organsiation_id
left join upload_table on test_master_table.master_id = upload_table.master_id
where organisation_table.organsiation_id = '1' and type = 'type-1'
group by test_master_table.master_id;*/

Answer 1

Simplifying the query first

Your particular query doesn't require a derived table. I'd rewrite it to this:

select 
  organisation_name, 
  sum(api_status = 'COMPLETED') as success_count, 
  sum(api_status <> 'COMPLETED') as failure_count,
  count(distinct transaction_id) as 'total'
from test_master_table 
  left join organsiations_table 
    on test_master_table.organisation_id = organsiations_table.organisation_id 
  left join upload_table 
    on test_master_table.master_id = upload_table.master_id
  left join request_table 
    on test_master_table.request_id = request_table.id
where (test_master_table.organisation_id = '1' 
  and (created_date >= current_timestamp())
group by test_master_table.master_id;

You'll lose absolutely nothing in terms of semantics, and it will be easier to translate to jOOQ. This is a frequent topic. A lot of times when people struggle creating derived tables in jOOQ, the problem could be circumvented this way. So frequent, I decided to write a blog post about this on the jOOQ blog.

Translating derived tables to jOOQ

The jOOQ manual section about derived tables shows how to do it. You have to assign the derived table to a local variable first:

Table<?> nested = select(...).from(...).groupBy(...).asTable("nested");

And then dereference columns from it, e.g.:

// If your field is an expression:
nested.field("field_name");

// If your field is from generated code, in case of which
// columns are matched by name:
nested.field(TABLE.COLUMN);

How to do it specifically

I'm aware you have a more specific question about this. I'll explain the specific problem there.