Total up values of Dynamic column and apply formula in a new column

Question

I have a below table structure :

CREATE EXTERNAL TABLE IF NOT EXISTS `samplecontrolid`(
  `controlId` string, 
  `status` string, 
 )

Sample Data :

controlid       status  
CloudTrail.1    PASSED      
CloudTrail.1    PASSED      
CloudTrail.1    PASSED      
CloudTrail.1    PASSED  
CloudTrail.1    FAILED  
CloudTrail.1    FAILED  
IAM.8           PASSED  
IAM.8           PASSED      
IAM.8           FAILED      
IAM.8           FAILED  
IAM.8           FAILED      
IAM.8           FAILED  

I want to count number of passed and failed for each ControlID:

Query i have written:

SELECT
   ControlId
  ,sum(case Status when 'PASSED' then 1 else 0 end) Passed
  ,sum(case Status when 'FAILED' then 1 else 0 end) Failed
 from samplecontrolid
 group by ControlId

Output:

ControlId    Passed Failed
CloudTrail.1    4   2
IAM.4           4   3
IAM.8           2   4
CIS3.2          5   0

Now, I want to have Total Column which Sum up Passed / Failed Value. Since Passed / Failed column value is generated on the fly , i cannot directly apply Sum function on these columns. How i can add up these values on the fly. Like below :

ControlId    Passed Failed  Total
CloudTrail.1    4   2        6 
IAM.4           4   3        7
IAM.8           2   4        6
CIS3.2          5   0        5

Finally, Once Total value is calculated. I need another column calculating the percentage.

Percentage formula =  Passed / Total * 100
Example for CloudTrail.1 = 4/6 * 100 = 66.66 

Final Output :

ControlId    Passed Failed  Total  Percentage
CloudTrail.1    4   2        6       66.66
IAM.4           4   3        7       57.14
IAM.8           2   4        6       33.33
CIS3.2          5   0        5       100

Answer 1

No need for as second pass

Example

SELECT  ControlId
       ,sum(case Status when 'PASSED' then 1 else 0 end) Passed
       ,sum(case Status when 'FAILED' then 1 else 0 end) Failed
       ,sum(1) Total
       ,avg( case when Status='PASSED' then 100.0 else 0.0 end) Pct
 from samplecontrolid
 group by ControlId

Results