Query to find an average weighted price

Question

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?

score 23 · Accepted Answer · answered Jan 19 '11 at 20:47

23

Try this:

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

answered Jan 19 '11 at 20:47

Chandu

81,493
19
133
134

Guess you don't need the TOTAL_QTY field anymore. It blatantly violates the unicity data principle - each piece of data must appear only once. – Josep May 29 '13 at 13:05

score 0 · Answer 2 · answered Feb 10 '16 at 13:34

create a user defined aggregate function to calculate the weighted average:

CREATE OR REPLACE TYPE WEIGHTED_AVG_O AS OBJECT ( 
  sum_of_weights NUMBER, 
  sum_of_weights_times_value NUMBER, 

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(cs_ctx IN OUT WEIGHTED_AVG_O) RETURN NUMBER, 
  MEMBER FUNCTION ODCIAGGREGATEITERATE   (self IN OUT WEIGHTED_AVG_O, value IN WEIGHTED_AVG_O) RETURN NUMBER, 
  MEMBER FUNCTION ODCIAGGREGATEMERGE     (self IN OUT WEIGHTED_AVG_O, ctx2 IN OUT WEIGHTED_AVG_O) RETURN NUMBER, 
  MEMBER FUNCTION ODCIAGGREGATETERMINATE (self IN OUT WEIGHTED_AVG_O, returnvalue OUT NUMBER, flags IN NUMBER) RETURN NUMBER 
);
/



CREATE OR REPLACE TYPE BODY WEIGHTED_AVG_O
AS

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(cs_ctx IN OUT WEIGHTED_AVG_O) RETURN NUMBER
  IS
  BEGIN
    cs_ctx := WEIGHTED_AVG_O(0, 0);
    RETURN odciconst.success;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE   (self IN OUT WEIGHTED_AVG_O, value IN WEIGHTED_AVG_O) RETURN NUMBER
  IS
  BEGIN
    self.sum_of_weights := self.sum_of_weights + value.sum_of_weights;
    self.sum_of_weights_times_value := self.sum_of_weights_times_value + value.sum_of_weights * value.sum_of_weights_times_value;
    RETURN odciconst.success;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE     (self IN OUT WEIGHTED_AVG_O, ctx2 IN OUT WEIGHTED_AVG_O) RETURN NUMBER
  IS
  BEGIN
    RETURN odciconst.success;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE (self IN OUT WEIGHTED_AVG_O, returnvalue OUT NUMBER, flags IN NUMBER) RETURN NUMBER 
  IS
  BEGIN    
    IF sum_of_weights = 0 THEN
      returnvalue := NULL;
    ELSE
      returnvalue := sum_of_weights_times_value / sum_of_weights;
    END IF;
    RETURN odciconst.success;
  END;

END;
/


CREATE OR REPLACE FUNCTION WEIGHTED_AVG (input WEIGHTED_AVG_O)
   RETURN NUMBER PARALLEL_ENABLE
   AGGREGATE USING WEIGHTED_AVG_O;
/

query with your data:

SELECT part, WEIGHTED_AVG(WEIGHTED_AVG_O(qty, price_per))
  FROM <YOUR_TABLE>
 GROUP BY part;

Query to find an average weighted price

2 Answers2

Linked