Count of a occurrence of specific string in multiple columns-CodePudding

I have a table as follows:

Auction_Date Auction_No   L1_Status   L2_Status  L3_Status 
2022-08-01   AUC00123     Acc         Rej        Acc
2022-08-01   AUC00122     Rej         Acc        Acc
2022-08-02   AUC00223     Acc         Acc        Acc
2022-08-12   AUC00221     Rej         Rej        Acc

Now I want to have another column where count of L1_Status, L2_Status and L3_Status would be shown when Status = 'Acc'. So the resultant table would be like

Auction_Date Auction_No   L1_Status   L2_Status  L3_Status   Count_Acc
2022-08-01   AUC00123     Acc         Rej        Acc             2
2022-08-01   AUC00122     Rej         Acc        Acc             2
2022-08-02   AUC00223     Acc         Acc        Acc             3
2022-08-12   AUC00221     Rej         Rej        Acc             1

I know one approach could be UNPIVOT the last 3 fields and then get Acc count. Then this UNPIVOT table would have to be joined after GROUP BY Auction_No with above table to get the Count_Acc field.

SELECT Auction_No, SUM(CASE WHEN L1_Status = 'Acc' THEN 1 ELSE 0 END)  
                   SUM(CASE WHEN L2_Status = 'Acc' THEN 1 ELSE 0 END)  
                   SUM(CASE WHEN L3_Status = 'Acc' THEN 1 ELSE 0 END) AS Count_Acc
FROM Table GROUP BY Auction_No

Is there any better approach?

CodePudding user response：

Your approach is already good, but I have a shorter approach :)

SELECT Auction_No
    , SUM(IIF(L1_Status='Acc',1,0)   IIF(L1_Status='Acc',1,0)   IIF(L1_Status = 'Acc',1,0)) as Count_Acc                   
FROM Table GROUP BY Auction_No