← 개발일지

Converting a Detail Query into a List Aggregate in Oracle SQL

oraclesqlmybatisaggregatesubquery

Converting a Detail Query into a List Aggregate in Oracle SQL

Here's a situation most backend developers have run into.

You already have a working detail page query — it pulls a per-budget breakdown for a specific member, handles edge cases, and returns exactly what you need. Now the list page needs a single "total available budget" column per member, and you'd rather not rewrite everything from scratch.

This post walks through the exact pattern for converting a single-record detail query into a per-entity aggregate query in Oracle SQL, using MyBatis as the mapper layer.

The Setup

Imagine a budget management system where each member has multiple budget allocations. The detail query works like this:

  • BAS subquery — aggregates issued, clawed-back, and used amounts per budget type, computes remaining balance
  • Unapproved subquery — sums order amounts that are paid but not yet approved
  • Final output: available budget = remaining balance − unapproved amount, one row per budget type

For the detail page, you filter with WHERE MBR_NO = #{memberId} and get N rows — one per budget type. For the list page, you need one row per member with the sum of all available budgets.

The Conversion Strategy

The logic is straightforward once you see it:

Remove the single-record WHERE filter. Wrap the GROUP BY one level higher.

Three concrete steps:

Step 1 — Drop the member filter

-- Remove this from the BAS subquery
AND BUDGET_TBL.MBR_NO = #{memberId}

Step 2 — Add MBR_NO to GROUP BY

The original BAS subquery grouped by budget type only. Now you need both.

-- Before
GROUP BY BUDGET_ID

-- After
GROUP BY MBR_NO, BUDGET_ID

Step 3 — Wrap with an outer SUM

Aggregate the per-budget remaining amounts up to the member level.

SELECT MBR_NO
     , SUM(REMAINING - NVL(UNAPPROVED_AMT, 0)) AS TOTAL_AVAILABLE
  FROM (
         -- Inner BAS subquery, now grouped by MBR_NO + BUDGET_ID
       ) BAS
  LEFT JOIN (
              -- Unapproved amounts subquery
            ) UNAPPR
         ON UNAPPR.MBR_NO    = BAS.MBR_NO
        AND UNAPPR.BUDGET_ID = BAS.BUDGET_ID
 GROUP BY MBR_NO

Plugging Into the List Query

Attach the aggregate subquery to your member list query via LEFT JOIN.

LEFT JOIN — not INNER JOIN — because members with no budget data still need to appear in the list. INNER JOIN would silently drop them.

SELECT B.MBR_NO
     , B.MBR_NM
     , D.DEPT_NM
     , NVL(BUDGET.TOTAL_AVAILABLE, 0) AS TOTAL_AVAILABLE
  FROM APPROVAL_TARGET_LIST A
 INNER JOIN MEMBER_BASE B   ON A.MBR_NO  = B.MBR_NO
 INNER JOIN MEMBER_DEPT C   ON B.MBR_NO  = C.MBR_NO
 INNER JOIN DEPT_BASE D     ON C.DEPT_NO = D.DEPT_NO
  LEFT JOIN (
    SELECT MBR_NO
         , SUM(REMAINING - NVL(UNAPPROVED_AMT, 0)) AS TOTAL_AVAILABLE
      FROM ...
     GROUP BY MBR_NO
  ) BUDGET ON BUDGET.MBR_NO = B.MBR_NO
 WHERE A.APPROVER_ID = #{approverId}

Reusing a Shared SQL Fragment in MyBatis

If your unapproved-amount query is already extracted as a <sql> fragment — and it starts with a WITH clause (CTE) — you can reuse it via <include> without duplicating code:

LEFT JOIN (
  SELECT MBR_NO, BUDGET_ID, TOTAL_AMOUNT
    FROM (
<include refid="com.example.mapper.OrderMapper.unapprovedAmountSql"/>
         )
) UNAPPR ON ...

This works, but there is a version caveat worth knowing.

Two Gotchas to Check First

1. Nesting a WITH Clause Inside an Inline View Requires Oracle 12c+

If the included fragment starts with WITH ... AS (...), embedding it inside a FROM (...) inline view only works on Oracle 12c and above. On 11g, you will get a syntax error.

-- Oracle 12c+: this works
FROM (
  WITH CTE AS (SELECT ...) SELECT ... FROM CTE
)

-- Oracle 11g: rewrite CTE as a regular inline view
FROM (
  SELECT ... FROM (SELECT ...) CTE_ALIAS ...
)

If you are already using the same <include> pattern elsewhere in your codebase and it runs fine, you are on 12c+ and can skip this concern.

2. DISTINCT in the Outer Query + Aggregate Join

If your list query uses SELECT DISTINCT, adding an aggregate LEFT JOIN is usually fine — the aggregate subquery returns exactly one row per member, so it will not introduce duplicates.

The problem arises when the driving table (an approval target list, for example) already has multiple rows for the same member. In that case, each row gets the same TOTAL_AVAILABLE value, and DISTINCT cannot collapse them because the rows look identical only on some columns.

The safe fix: pre-deduplicate the driving table before joining anything else.

-- Deduplicate first, then join
FROM (
  SELECT DISTINCT MBR_NO
    FROM APPROVAL_TARGET_LIST
   WHERE APPROVER_ID = #{approverId}
) A
INNER JOIN MEMBER_BASE B ON A.MBR_NO  = B.MBR_NO
 LEFT JOIN (...) BUDGET  ON BUDGET.MBR_NO = B.MBR_NO

Summary

| | Detail query | List aggregate query | |---|---|---| | Filter | WHERE MBR_NO = #{id} | Removed | | GROUP BY | BUDGET_ID | MBR_NO, BUDGET_ID then outer GROUP BY MBR_NO | | Output | N rows (one per budget) | 1 row per member | | Join | Standalone | LEFT JOIN (...) ON MBR_NO |

The core idea: raise the aggregation level. Detail queries aggregate at the child level (budget type); list queries aggregate at the parent level (member). One extra GROUP BY wrapper and the transformation is done.

Check the WITH clause version constraint and the DISTINCT collision risk upfront — the rest is mechanical.