一个关于计算的SQL语句,高手请进.

Question

SELECT 
    BigClass.bName,
    count(eid) as [count]
FROM 
    BigClass 
INNER JOIN 
    Equipment 
ON 
    BigClass.bID = Equipment.Bclass 
group by 
    bname

Answer 1

补充一下:表结构:表Bigclass为大类名表:
Bid(自动) Bname(类名)
-----------------
1       电脑设备
2       电脑耗材
.....表Equipment为设备清单表:
Eid(自动)      Pcode(产品名)    bclass(大类ID)   buyPrice(购买价格)    
----------------------------------------------------------------------
1              DA-00100             1             2300
2              DE-00100             2             5000
3              DA-00101             1             3500
4              DE-00100             2             6600
......................请问,如何用SQL语句,一次性的打印出:大类名,该大类数量,该大类购买价格总值,该大类数量占所有产品数量的百分比,该大类购买价格总值占所有产品的百分比?比效难,高手帮看看.谢了.

Answer 2

select 大类名=bclass,
       该大类数量=(select sum(count) from Equipment where bclass=a.bclass),
       该大类购买价格总值=(select sum(buprice) from Equipment where bclass=a.bclass),
       该大类数量%=cast(((select sum(count) from Equipment where bclass=a.bclass)/((select sum(count) from Equipment))*100 as varchar)+'%',
       该大类购买价格总值=cast(((select sum(buprice) from Equipment where bclass=a.bclass)/((select sum(buprice) from Equipment))*100 as varchar)+'%',
from Equipment a
group by a.bclass        

Answer 3

select a.bname as 大类名,count(b.bclass) as 大类数量,sum(b.buyprice) as 总价值,
cast(count(b.bclass)/(select count(*) from equipment)*100 as varchar) as [数量比%],
cast(sum(b.buyprice)/(select sum(buyprice from equipment))*100 as varchar) as [价值比%]
from equipment b,bigclass a where b.bclass=a.bid group b.bclass