I want to calculate the Machine compliance percentages and the counts.
Here is the sample data:
Machine 
Environment 
Status 
GA 
PROD 
Compliance 
GA 
PROD 
Non Compliance 
NY 
NonProd 
Compliance 
NY 
NonProd 
Non Compliance 
CA 
PROD 
Non Compliance 
CA 
PROD 
Compliance 
CA 
PROD 
Non Compliance 
CA 
NonProd 
Compliance 
WA 
PROD 
Non Compliance 
WA 
NonProd 
Compliance 
DN 
PROD 
Compliance 
DN 
PROD 
Non Compliance 
Expected Output 










Prod 



NonProd 




Machine 
ProdTotal 
Compliance 
NonCompliance 
Prodcompliance% 
Compliance 
NonCompliance 
NonProdTotal 
NonProdCompliance% 
TotalProdNonProd 
GA 
2 
1 
1 
50% 
0 
0 
0 

2 
NY 
0 
0 
0 

1 
1 
2 
50% 
2 
CA 
3 
1 
2 
33.33% 
1 
0 
1 
100% 
4 
WA 
1 
0 
1 

1 
0 
1 
100% 
3 
DN 
2 
1 
1 
50% 
0 
0 
0 

2 
Total 
8 
3 
5 
375% 
3 
1 
4 
75% 
13 
Formula to calculate Percentages: 









Prodcompliance% 

ProdCompliance/ProdTotal 







NonProdCompliance% 

NonProdCompliance/NonProdTotal 







Thank you for your help in advance !!
hi
hope this helps
its just a starting point
please click arrow to the left for CREATE Sample data Script
create table Helping_sqlfresher_Hope_he_Gives_million_dollars
(
Machine varchar(100), Environment varchar(100), Status varchar(100)
)
go
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'GA','PROD','Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'GA','PROD','Non Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'NY','NonProd','Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'NY','NonProd','Non Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'CA','PROD','Non Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'CA','PROD','Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'CA','PROD','Non Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'CA','NonProd','Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'WA','PROD','Non Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'WA','NonProd','Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'DN','PROD','Compliance'
insert into Helping_sqlfresher_Hope_he_Gives_million_dollars select 'DN','PROD','Non Compliance'
go
select 'Sample Data',* from Helping_sqlfresher_Hope_he_Gives_million_dollars
go
; with cte_total_count_machine as
(
select machine,count(*) as mach_tot_cnt,sum(case when Environment = 'PROD' and Status = 'Compliance' then 1 else 0 end ) as cnt_comp from Helping_sqlfresher_Hope_he_Gives_million_dollars group by machine
) , cte_tot as
(
select 'Total' as machine ,sum(mach_tot_cnt) as tot_mach,sum(cnt_comp) as tot_comp from cte_total_count_machine
)
select * from cte_total_count_machine
union all
select *from cte_tot
go
1 Like