Jooq ERROR: missing FROM-clause entry for table for nested query(sum and group by)

Unnamed derived tables are supported only in a few SQL dialects (e.g. Oracle), not in all (e.g. not in PostgreSQL). This is why jOOQ generates an alias for you for every derived table. Now that your derived table is aliased, you can no longer access its columns using the original table names, but you have to use the alias instead. You can see where this went wrong in your generated SQL query:

select 
  "alias_88420990"."ledger_id", -- These are correctly referenced, because you used
  "alias_88420990"."amount"     -- select(), so jOOQ did the dereferencing for your
from (
  select 
    "public"."test_safes_funds_allocation"."ledger_id", 
    sum("public"."test_safes_funds_allocation"."amount") as "amount"
  from "public"."test_safes_funds_allocation" 
  group by "public"."test_safes_funds_allocation"."ledger_id"
) as "alias_88420990" -- This alias is generated by jOOQ

-- In these predicates, you're referencing the original column name with full qualification
-- when you should be referncing the column from alias_88420990 instead
where ("public"."test_safes_funds_allocation"."amount" >= ? and 1 = 1) 
order by "public"."test_safes_funds_allocation"."amount" asc 
limit ?

Canonical fix

So your jOOQ query could be rewritten as such in order to get the table names right:

// Create a local variable to contain your subquery. Ideally, provide an explicit alias
Table<?> t = table(
    select(
        TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID,       
        sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT)
           .as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))
   .from(TEST_SAFES_FUNDS_ALLOCATION)
   .groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)).as("t");

// Now, use t everywhere, instead of TEST_SAFES_FUNDS_ALLOCATION
context.select()
       .from(t)
       .where(t.field(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).greaterOrEqual(amount))
       .and(t.field(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID).notIn(excludedLedgers))
       .orderBy(t.field(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).asc())
       .limit(1)
       .fetchOne();

I'm using Table.field(Field) to extract a field from an aliased table, without losing type information.

Improved fix, leveraging existing table types

Given that your derived has two columns whose name also appear in the original table, you could use a "trick"to get more type safety out of the jOOQ API, and thus a more convenient way to dereference the columns. Alias the table first:

// This t reference now has all the column references like the original table
TestSafesFundsAllocation t = TEST_SAFES_FUNDS_ALLOCATION.as("t");

// The subquery is also named "t", but has a different definition
Table<?> subquery = table(
    select(
        TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID,       
        sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT)
           .as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))
   .from(TEST_SAFES_FUNDS_ALLOCATION)
   .groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)).as(t);

// Now, select again from the subquery, but dereference columns from the aliased table t
context.select()
       .from(subquery)
       .where(t.AMOUNT.greaterOrEqual(amount))
       .and(t.LEDGER_ID.notIn(excludedLedgers))
       .orderBy(t.AMOUNT.asc())
       .limit(1)
       .fetchOne();

Remember, this is a trick, which only works as long as the derived table's columns have the same names and types as the original table which the derived table selects from.

Rewrite your SQL

Derived tables (and common table expressions) are an area where jOOQ's DSL isn't just as powerful as native SQL because jOOQ cannot easily type check a derived table in the usual way. This is why local variables and type unsafe dereferencing has to be used.

Often, this caveat is a good enough reason to avoid derived tables entirely, if this is an option. In your case, it is, you don't need a derived table. A better query (even better in native SQL) would be:

context.select(
          TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID, 
          sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))
       .from(TEST_SAFES_FUNDS_ALLOCATION)
       .where(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID.notIn(excludedLedgers))
       .groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)
       .having(sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).greaterOrEqual(amount))
       .orderBy(sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).asc())
       .limit(1)
       .fetchOne()