with main as (

select 

*, 
case 
             when status = 'RJCT' AND status_reason = 'NARR' AND signing_status = 'SIGNED' THEN 'Rejected(error)/Unknown'
             when status = 'RJCT' AND status_reason != 'NARR' THEN 'Rejected'
             when status = 'PDNG' AND status_reason is null THEN 'Unknown'
             when status = 'PDNG' AND status_reason = 'NARR' and signing_status = 'SIGNED' THEN 'onHold/PendingUserConfirmation'
             when status = 'PDNG' AND status_reason = 'DS0A' and signing_status = 'SIGNED' THEN 'PartiallyConfirmed'
             when status = 'PDNG' AND signing_status = 'PENDING' THEN 'PendingConfirmation'
             when status = 'PDNG' AND signing_status = 'SIGNING' THEN 'PendingUserApproval'
             when status = 'PDNG' AND signing_status = 'CANCELLED' THEN 'UserApprovalCancelled'             
             when status = 'PDNG' AND signing_status = 'EXPIRED' THEN 'UserApprovalTimeout'             
             when status = 'PDNG' AND signing_status = 'FAILED' THEN 'UserApprovalFailed'
             when status = 'PDNG' AND signing_status = 'SIGNED' THEN 'Unknown'
             when status = 'ACCP' THEN 'Paid'
             when status = 'ACSP' AND signing_status = 'PENDING' THEN 'PendingConfirmation'
             when status = 'ACSP' AND signing_status = 'SIGNED' THEN 'Confirmed'
             when status = 'ACSP' AND signing_status = 'EXPIRED' THEN 'UserApprovalTimeout'
             when status = 'ACSP' AND 
 signing_status = 'FAILED' THEN 'UserApprovalFailed'
             when status = 'ACWC' AND status_reason = 'NARR' AND signing_status = 'SIGNED' THEN 'Paid'
             when status = 'ACWC' AND status_reason = 'NARR' AND signing_status = 'SIGNED' THEN 'Paid'
             when status = 'ACWC' AND status_reason != 'NARR' THEN 'PendingConfirmation'
             when status = 'EXECUTING' AND signing_status = 'SIGNED' THEN 'Confirmed'
             when status = 'EXECUTING' AND signing_status = 'PENDING' THEN 'Confirmed'
             when status = 'FAILED' THEN 'UserApprovalFailed'
            ELSE 'UNKNOWN' END
as new_status

from main payments

)

select 
new_status,
count(*) as rows_per_status
from main
group by 1

只需将您的查询作为子查询，如下所示：

SELECT
    STATUS "STATUS", 
    Sum(STAT_COUNT) "TOTAL_STAT_COUNT"
FROM
    (
        Select 
            case 
                 when stats.status = 'RJCT' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Rejected(error)/Unknown'
                 when stats.status = 'RJCT' AND stats.status_reason != 'NARR' THEN 'Rejected'
                 when stats.status = 'PDNG' AND stats.status_reason is null THEN 'Unknown'
                 when stats.status = 'PDNG' AND stats.status_reason = 'NARR' and stats.signing_status = 'SIGNED' THEN 'onHold/PendingUserConfirmation'
                 when stats.status = 'PDNG' AND stats.status_reason = 'DS0A' and stats.signing_status = 'SIGNED' THEN 'PartiallyConfirmed'
                 when stats.status = 'PDNG' AND stats.signing_status = 'PENDING' THEN 'PendingConfirmation'
                 when stats.status = 'PDNG' AND stats.signing_status = 'SIGNING' THEN 'PendingUserApproval'
                 when stats.status = 'PDNG' AND stats.signing_status = 'CANCELLED' THEN 'UserApprovalCancelled'             
                 when stats.status = 'PDNG' AND stats.signing_status = 'EXPIRED' THEN 'UserApprovalTimeout'             
                 when stats.status = 'PDNG' AND stats.signing_status = 'FAILED' THEN 'UserApprovalFailed'
                 when stats.status = 'PDNG' AND stats.signing_status = 'SIGNED' THEN 'Unknown'
                 when stats.status = 'ACCP' THEN 'Paid'
                 when stats.status = 'ACSP' AND stats.signing_status = 'PENDING' THEN 'PendingConfirmation'
                 when stats.status = 'ACSP' AND stats.signing_status = 'SIGNED' THEN 'Confirmed'
                 when stats.status = 'ACSP' AND stats.signing_status = 'EXPIRED' THEN 'UserApprovalTimeout'
                 when stats.status = 'ACSP' AND stats.signing_status = 'FAILED' THEN 'UserApprovalFailed'
                 when stats.status = 'ACWC' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Paid'
                 when stats.status = 'ACWC' AND stats.status_reason = 'NARR' AND stats.signing_status = 'SIGNED' THEN 'Paid'
                 when stats.status = 'ACWC' AND stats.status_reason != 'NARR' THEN 'PendingConfirmation'
                 when stats.status = 'EXECUTING' AND stats.signing_status = 'SIGNED' THEN 'Confirmed'
                 when stats.status = 'EXECUTING' AND stats.signing_status = 'PENDING' THEN 'Confirmed'
                 when stats.status = 'FAILED' THEN 'UserApprovalFailed'
            ELSE 
            'UNKNOWN' 
            END "STATUS", 
            stats.STAT_COUNT "STAT_COUNT"
        From 
        (   
            Select STATUS, STATUS_REASON, SIGNING_STATUS, Count(*) "STAT_COUNT" From PAYMENTS
            Group By STATUS, SIGNING_STATUS, STATUS_REASON
        ) stats;
    )
GROUP BY STATUS

此致