sql; distribución porcentual con valores contados

Tengo una tabla (Salarios) con salarios distribuidos en género y título de trabajo. Quiero mostrar los resultados en género para cada título de trabajo que tengo en mi database más el valor total. Por ejemplo, para el primer titel tengo este código:

select 'Female' as '____', count(gender) as Number, SUM(Salary) AS Total_Salary, MIN(salary) AS Min_salary, AVG(salary) AS Avg_Salary, MAX(salary) AS Max_Salary from Salaries where gender= 'Female' And Job_titel='Adm assistent' union ( select 'Man' as '____', count(gender) as Number, SUM(Salary) AS Total_Salary, MIN(salary) AS Min_salary, AVG(salary) AS Avg_Salary, MAX(salary) AS Max_Salary from Salaries where gender= 'Man' AND Job_titel='Adm assistent') union ( select 'Total' as '____', count(gender) as Number, SUM(Salary) AS Total_Salary, MIN(salary) AS Min_salary, AVG(salary) AS Avg_Salary, MAX(salary) AS Max_Salary from Salaries where Job_titel ='Adm assistent' ) 

La table:

  ____ Number Tot_Salaries Min_Salaries Avg_Salaries Max_Salaries ---------------------------------------------------------- Female 142 1 000 000 XXXXXX XXXXXXX XXXXXXX Male 18 300 000 XXXXXX XXXXXXX XXXXXXX Total 160 1 300 000 XXXXXX XXXXXXX XXXXXXX ---------- 

Ahora quiero que la tabla tenga la distribución en% para el género y salarios como este:

  ___ Number %_Gender Tot_Salaries % Min_Salaries Avg_Salaries Max_Salaries Female 142 89 1 000 000 77 XXXXXX XXXXXXX XXXXXXX Male 18 11 300 000 13 XXXXXX XXXXXXX XXXXXXX Total 160 100 1 300 000 00 XXXXXX XXXXXXX XXXXXXX ---------- 

¿Cómo obtengo estos dos valores de distribución? Creo que mi código quizás sea bastante ineficaz en este momento 🙂 Uso el server MS sql.

esto sería mucho más optimizado. No es necesario realizar consultas en la tabla de Sueldos varias veces.

 SELECT Job_title,gender, count(gender) as Number, SUM(salary) AS Total_Salary, MIN(salary) AS Min_salary, AVG(salary) AS Avg_Salary, MAX(salary) AS Max_Salary INTO #t FROM Salaries WHERE Job_title ='Adm assistent' GROUP BY Job_title, gender SELECT t.Job_title, t.gender, t.Number, CASE WHEN t2.tot_num = 0 THEN 0 ELSE t.Number/CAST(t2.tot_num AS float) * 100 END as [pct_gender], t.Total_Salary, t.Min_salary, t.Avg_Salary, t.Max_Salary FROM #tt INNER JOIN (SELECT Job_title, SUM(Number) AS tot_num FROM #t GROUP BY Job_title) t2 ON t2.Job_title = t.Job_title UNION SELECT t.Job_title, 'Total', SUM( t.Number), 100 as [pct_gender], SUM(t.Total_Salary), SUM(t.Min_salary) , SUM(t.Avg_Salary), SUM( t.Max_Salary) FROM #tt GROUP BY t.Job_title ORDER BY Job_title,Number DROP TABLE #t 

Puede usar 2 variables declaradas para totalcount y totalsalary

 declare @totalgender decimal(12,1) declare @totalSalary decimal(12,1) select @totalgender=(select count(gender) as total from Salaries where Job_titel ='Adm assistent' ) select @totalSalary=(select SUM(Salary) as total from Salaries where Job_titel ='Adm assistent' ) select 'Female' as '____', count(Salaries.gender) as Number, cast(count(Salaries.gender) / @totalgender*100 as int), SUM(Salary) AS Total_Salary, cast(SUM(Salary) / @totalSalary*100 as int), MIN(salary) AS Min_salary, AVG(salary) AS Avg_Salary, MAX(salary) AS Max_Salary from Salaries where gender= 'Female' And Job_titel='Adm assistent' union ( select 'Man' as '____', count(gender) as Number, cast(count(Salaries.gender) / @totalgender*100 as int), SUM(Salary) AS Total_Salary, cast(SUM(Salary) / @totalSalary*100 as int), MIN(salary) AS Min_salary, AVG(salary) AS Avg_Salary, MAX(salary) AS Max_Salary from Salaries where gender= 'Man' AND Job_titel='Adm assistent') union ( select 'Total' as '____', count(gender) as Number, cast(count(Salaries.gender) / @totalgender*100 as int), SUM(Salary) AS Total_Salary, cast(SUM(Salary) / @totalSalary*100 as int), MIN(salary) AS Min_salary, AVG(salary) AS Avg_Salary, MAX(salary) AS Max_Salary from Salaries where Job_titel ='Adm assistent' ) 

o join table with select (select Job_titel, cast(count(gender )as decimal(5,2)) as total from Salaries where Job_titel ='Adm assistent' group by Job_titel) stot

 select 'Female' as '____', count(s.gender) as Number, max(stot.total), cast(count(s.gender)/max(stot.total)*100 as int), SUM(s.Salary) AS Total_Salary, MIN(s.salary) AS Min_salary, AVG(s.salary) AS Avg_Salary, MAX(s.salary) AS Max_Salary from Salaries s inner join (select Job_titel, cast(count(gender )as decimal(5,2)) as total from Salaries where Job_titel ='Adm assistent' group by Job_titel) stot on s.Job_titel= stot.Job_titel where gender= 'Female' And s.Job_titel='Adm assistent' union select 'Man' as '____', count(s.gender) as Number, max(stot.total), cast(count(s.gender)/max(stot.total)*100 as int), SUM(s.Salary) AS Total_Salary, MIN(s.salary) AS Min_salary, AVG(s.salary) AS Avg_Salary, MAX(s.salary) AS Max_Salary from Salaries s inner join (select Job_titel, cast(count(gender )as decimal(5,2)) as total from Salaries where Job_titel ='Adm assistent' group by Job_titel) stot on s.Job_titel= stot.Job_titel where gender= 'Man' And s.Job_titel='Adm assistent' union select 'Total' as '____', count(s.gender) as Number, max(stot.total), cast(count(s.gender)/max(stot.total)*100 as int), SUM(s.Salary) AS Total_Salary, MIN(s.salary) AS Min_salary, AVG(s.salary) AS Avg_Salary, MAX(s.salary) AS Max_Salary from Salaries s inner join (select Job_titel, cast(count(gender )as decimal(5,2)) as total from Salaries where Job_titel ='Adm assistent' group by Job_titel) stot on s.Job_titel= stot.Job_titel where S.Job_titel ='Adm assistent'