百分比统计问题

Question

我有一个数据表DEPARTMENT,用来存储部门人员资料,结构如下:
Department  counter
===============================
A           10
B           68
C           45
D           23
E           20
F           10
G           10
H           30
I           90
J           100现在我想写一条SQL语句,根据每一部门人数,统计该部门人数在所有人数中的百分比,我想输出的结果如下:Department  counter   percent
===============================
A           10        5%
B           68        10%
C           45        8%
D           23        15%
E           20        15%
F           10        5%
G           10        4$
H           30        25%
I           90        30%
J           100       40%

Answer 1

declare @a table(Department  varchar(100),counter int)
insert @a select 'A',           10 
union all select 'B',           68 
union all select 'C',           45 
union all select 'D',          23 
union all select 'E',           20 
union all select 'F',           10 
union all select 'G',           10 
union all select 'H',           30 
union all select 'I',           90 
union all select 'J',           100 
select department, counter,ltrim(str(counter*1.0/(select sum(counter) from @a)*100,15,2))+'%' [percent] from @a
--result
/*department                     counter     percent          
------------------------------ ----------- ---------------- 
A                              10          2.46%
B                              68          16.75%
C                              45          11.08%
D                              23          5.67%
E                              20          4.93%
F                              10          2.46%
G                              10          2.46%
H                              30          7.39%
I                              90          22.17%
J                              100         24.63%（所影响的行数为 10 行）
*/

Answer 2

create table tb(Department varchar(10), counter int)
insert into tb values('A',           10 )
insert into tb values('B',           68 )
insert into tb values('C',           45 )
insert into tb values('D',           23 )
insert into tb values('E',           20 )
insert into tb values('F',           10 )
insert into tb values('G',           10 )
insert into tb values('H',           30 )
insert into tb values('I',           90 )
insert into tb values('J',           100)
goselect t1.* , [percent] = cast(cast((t1.counter*1.0/t2.cnt)*100 as decimal(18,2)) as varchar(5)) + '%'  from 
(select Department , sum(counter) counter from tb group by Department) t1,
(select sum(counter) cnt from tb) t2drop table tb
/*
Department counter     percent 
---------- ----------- ------- 
A          10          2.46%
B          68          16.75%
C          45          11.08%
D          23          5.67%
E          20          4.93%
F          10          2.46%
G          10          2.46%
H          30          7.39%
I          90          22.17%
J          100         24.63%（所影响的行数为 10 行）
*/

Answer 3

create table tb(Department varchar(10), counter int)
insert into tb values('A',           10 )
insert into tb values('B',           68 )
insert into tb values('C',           45 )
insert into tb values('D',           23 )
insert into tb values('E',           20 )
insert into tb values('F',           10 )
insert into tb values('G',           10 )
insert into tb values('H',           30 )
insert into tb values('I',           90 )
insert into tb values('J',           100)
goselect Department , sum(counter) counter , 
       [percent] = cast(cast((sum(counter)*1.0/(select sum(counter) from tb))*100 as decimal(18,2)) as varchar(5)) + '%' 
from tb group by Departmentdrop table tb
/*
Department counter     percent 
---------- ----------- ------- 
A          10          2.46%
B          68          16.75%
C          45          11.08%
D          23          5.67%
E          20          4.93%
F          10          2.46%
G          10          2.46%
H          30          7.39%
I          90          22.17%
J          100         24.63%（所影响的行数为 10 行）
*/

Answer 4

create table DEPARTMENT(Department varchar(50),counters int)
insert into DEPARTMENT select 'A',10
insert into DEPARTMENT select 'B',68
insert into DEPARTMENT select 'C',45
insert into DEPARTMENT select 'D',23
insert into DEPARTMENT select 'E',20
insert into DEPARTMENT select 'F',10
insert into DEPARTMENT select 'G',10
insert into DEPARTMENT select 'H',30
insert into DEPARTMENT select 'I',90
insert into DEPARTMENT select 'J',100select DEPARTMENT.Department,cast(cast(DEPARTMENT.counters*1.0/t.counts*100 as numeric(5,2)) as varchar(10))+'%'
from DEPARTMENT,(select sum(counters) as counts from DEPARTMENT)t