sql具有计数值的百分比分布 [英] sql; percent distribution with counted values

问题描述

我有一张表（薪水），薪金按性别和工作标签分配。
我想显示我的数据库中每个作业标题的性别结果加上总值。例如对于第一个标题我有这个代码：

I have a table (Salaries) with salaries distributed on gender and job titel. I want to show the results on gender for every job titel I have in my database plus the total value. For example for the first titel I have this code:

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' )

/ h2>

The 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
----------

现在我想要表格的性别为％的分布，例如：

Now I want the table to have the distribution in % for gender an salaries like this:

   ___  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
----------

如何获得这两个distrubution值？我认为我的代码也许现在是非常无效的:-)我使用MS的sql服务器。

How do I get this two distrubution values? I Think my code maybe is quite ineffective right now:-)I use MS sql server.

推荐答案

总计和总计

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' )

或连接表与选择（选择Job_titel， ）作为总计，从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'  

这篇关于sql具有计数值的百分比分布的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！