Menu
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
Ideas?
822
The weighted average function seq_wavg() calculates the average as the sum of the products of the two sequence elements divided by the sum of sequence 1.
Understanding the Volume-Weighted Average Price VWAP is calculated by totaling the dollars traded for every transaction (price multiplied by the volume) and then dividing by the total shares traded.
In mathematics and statistics, you calculate weighted average by multiplying each value in the set by its weight, then you add up the products and divide the products' sum by the sum of all weights. As you see, a normal average grade (75.4) and weighted average (73.5) are different values.
To calculate the weighted average in Excel, you must use the SUMPRODUCT and SUM functions using the following formula: =SUMPRODUCT(X:X,X:X)/SUM(X:X) This formula works by multiplying each value by its weight and combining the values. Then, you divide the SUMPRODUCT but the sum of the weights for your weighted average.
Try this:
SELECT part, SUM(qty*price_per)/SUM(qty)
FROM <YOUR_TABLE>
GROUP BY part
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
