SQLTeam.com | Weblogs | Forums

Calculate percentages and counts

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

image

1 Like