查询以找到平均加权价格 [英] Query to find an average weighted price

问题描述

我在Oracle中有一个表，每个给定部分有多行.每行都有一个与之关联的数量和价格.给定零件的行集合也有一个总数.以下是数据示例.我需要得到零件的平均加权价格.例如，如果数量为100的零件的价格为1，数量为50的零件的价格为2，则加权平均价格为1.33333333

I have a table in Oracle with multiple rows per a given part. Each row has a quantity and a price associated with it. There is also a total quantity that the set of rows for a given part adds up to. Below is a sample of the data. What I need is to get the average weighted price for the part. For example if a quantity of 100 of a part has a price of 1 and a quantity of 50 has a price of 2 the weighted average price is 1.33333333

PART   TOTAL_QTY  QTY   PRICE_PER
----------------------------------
part1  317        244   27
part1  317        40    53.85
part1  317        33    24.15

想法?

推荐答案

尝试一下:

SELECT part, SUM(qty*price_per)/SUM(qty)
  FROM <YOUR_TABLE>
GROUP BY part

这篇关于查询以找到平均加权价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！