百分比统计有关问题

百分比统计问题
我有一个数据表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%

[解决办法]

SQL code

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 行）*/
[解决办法]
SQL code
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) 
 go 
  
 select 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 Department 
  
 drop 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 行） 
 */

[解决办法]
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',100

select 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