27,579
社区成员
发帖
与我相关
我的任务
分享
--补充
select ck,djh,avg(zje)aje,sum(sl)*1./count(djh)
from tb
group by ck,djh
select ck,djh,avg(zje)aje,sum(sl)
from tb
group by ck,djh
select ck,djh,avg(zje),avg(sl)
from tb
group by ck,djh
declare @ckmx table
(ck varchar(2),djh varchar(2),hpdj decimal(6,2),sl int,xh int,zje decimal(6,2))
insert into @ckmx
select '01','01',10,1,1,10 union all
select '01','01',5,2,2,10 union all
select '01','02',2,1,1,2 union all
select '02','01',3,1,1,3
select ck,
[每张订单的平均金额]=sum(isnull(zje,0.00))/sum(isnull(sl,0)) ,
平均每张单据的货品数量=cast(sum(isnull(sl,0)) as float) /count(djh)
from @ckmx group by ck
/*
ck 每张订单的平均金额 平均每张单据的货品数量
---- --------------------------------------- ----------------------
01 5.500000 1.33333333333333
02 3.000000 1
*/
--1:
select ck,djh,avg(zje)aje
from tb
group by ck,djh
--2:
select ck,sum(sl)*1./(select count(distinct djh) from tb where ck = t.ck)
from tb t
group by ck