Agreggate rows from with same value caluculated in nested query

Im struggling with some problem. I want to aggregate rows with the same value of "STATUS" and count elements. The problem is, that "STATUS" column is calculated in nested query inside.

Query:


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) 
as status, stats.count
from (select status, status_reason, signing_status, count(*) as count from payments
group by status, signing_status, status_reason) stats;

Result:

Unknown 125
onHold/PendingUserConfirmation  15
Confirmed   12
UserApprovalFailed  41
UNKNOWN 22
Rejected(error)/Unknown 2
Rejected    176
Paid    1089
Rejected    72
PendingConfirmation 219
Unknown 7
PendingConfirmation 14835
UserApprovalFailed  10
Rejected    13
UserApprovalTimeout 145
Rejected    2
Paid    2
UNKNOWN 1
UserApprovalFailed  355
Unknown 13
PartiallyConfirmed  301
Confirmed   510
Unknown 2
PendingConfirmation 1
UserApprovalFailed  4
Unknown 13
Unknown 5
Confirmed   2399
Rejected    1
Rejected    5
Confirmed   7
Confirmed   170
Unknown 2
UserApprovalTimeout 1
PendingConfirmation 29
UserApprovalFailed  82
UserApprovalFailed  8
UserApprovalCancelled   14
UNKNOWN 1
UserApprovalFailed  16
Rejected    11
PendingConfirmation 76
PendingConfirmation 3
UserApprovalTimeout 6
Paid    2
Paid    1736
PendingUserApproval 790
UserApprovalTimeout 551
Confirmed   9
Paid    288

I wish rows would be summed up by STATUS column. I wouldnt like to rebuild whole CASE function. Is there any solution for that?

CodePudding user response：


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

CodePudding user response：

Just make your query as subquery like here:

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

Regards...