计算平均值解决思路

计算平均值

SQL code

 CODE                QTY                TAXUPPMA01002000    18000.0000    15.93000000PMA01002000    455.0000    15.93000000PMA01002000    11869.0000    16.65000000PMA01002000    100.0000    15.72000000PMA01002000    5624.0000    15.45000000

计算CODE='PMA01002000'的TAXUP的平均值，它没次的TAXUP的值不同，所以计算的方法是（（第一条的QTY*TAXUP）+(第二条的QTY*TAXUP)）/第一条的跟第二条的总数QTY，有多少条记录那么就加几条它的QTY*TAXUP，再除以他们QTY的总和，
不知道我要求说清楚了没有。

[解决办法]

SQL code

select sum(qty*taxup)/count(taxup) from TB where code = 'PMA01002000'
[解决办法]
SQL code
select code,sum(qty+taxup)/sum(qty) as colfrom tbwhere code = '...'group by code
[解决办法]
SQL code
select code,sum(qty*taxup)/sum(qty)from tb group by code
[解决办法]
SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (CODE nvarchar(22),QTY numeric(9,4),TAXUP numeric(10,8))insert into [TB]select 'PMA01002000',18000.0000,15.93000000 union allselect 'PMA01002000',455.0000,15.93000000 union allselect 'PMA01002000',11869.0000,16.65000000 union allselect 'PMA01002000',100.0000,15.72000000 union allselect 'PMA01002000',5624.0000,15.45000000select * from [TB]select sum(qty*taxup)/count(taxup) from TB where code = 'PMA01002000'--116013.960000000000 

 

查看更多 下一篇