求统计语句SQL

[Mirror然]

create table tb(name varchar(10) , [type] varchar(10) , sort int, sale float, Qsale float)
insert into tb values('A' , 'A01' , 10, 100, 108)
insert into tb values('A' , 'A02' , 20, 200, 210)
insert into tb values('A' , 'A02' , 20, 300, 350)
insert into tb values('A' , 'A02' , 20, 350, 400)
insert into tb values('A' , 'A03' , 30, 285, 300)
insert into tb values('A' , 'A03' , 30, 150, 180)


---结果
[type] 总金额 金额比率(sum(Qsale)/sum(sale)-1)     
A01     100     0.08 
A02     850     0.12 (210+350+400)/(200+300+350)-1 = 0.12
A03     435     0.10

[xxfvba]

select TYPE,SUM(sale) as [总金额],left(convert(varchar,SUM(qsale)/SUM(sale)-1),4) as [金额比率] from tb group by type ------------------------------------------------------------------------------------------- Type 总金额 金额比率 A01 100 0.08 A02 850 0.12 A03 435 0.10

[t101lian]

select type, sum(sale)  as '总金额', 
convert(decimal(9,2),(sum(qsale)/sum(sale)-1) )as '金额比率'
 from tb group by type

[KeepSayingNo]

select [type],SUM(sale) '总金额',convert(numeric(10,2),SUM(Qsale)/SUM(sale)-1) '金额比例' 
from tb group by [type]

[Mirror然]

求达人

[iailp4728]

select type, sum(sale), sum(qsale),convert(decimal(9,2),(sum(qsale)/sum(sale)-1) ) from tb group by type