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% |
|
Prod-Compliance/ProdTotal |
|
|
|
|
|
|
|
| NonProdCompliance% |
|
NonProd-Compliance/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