Calculate Weighted Average in sql for duplicate items

Question

TABLE

ITEM |  SKU  |  QUANT  |      ITEM LANDING
-----+-------+---------+-----------------
ABC  | A-B-C |     5   |         500  
ABC  | A-B-C |    10   |         400  
DEF  | D-E-F |     5   |         200  
GHI  | G-H-I |     6   |         300  
JKL  | J-K-L |     5   |         800  
JKL  | J-K-L |    10   |         600

RESULT

ITEM NAME | ITEM SKU  | QUANT  |  Weighted Average
----------+-----------+--------+---------------------
  ABC     |    A-B-C  |   15   |      433.33  
  DEF     |    D-E-F  |    5   |      200  
  GHI     |    G-H-I  |    6   |      300  
  JKL     |    J-K-L  |   15   |      666.66

I want to calculate weighted average for duplicate items
((first landing *qty)+(second landing *qty))/(sum(qty)

What is that? Please format your information, tell us what you need, and what you tried. — schlonzo, Jul 29 '14 at 07:59

score 7 · Accepted Answer · answered Jul 29 '14 at 08:04

7

SELECT ITEMNAME, ITEMSKU, SUM(QUANT), SUM(QUANT * ITEM_LANDING) / SUM(QUANT)
FROM <YourTable>
GROUP BY ITEMNAME, ITEMSKU

answered Jul 29 '14 at 08:04

schlonzo

1,409
13
16

Ullas · Answer 2 · 2014-09-18T05:32:41.650

2

Try this...

QUERY

SELECT item_name AS 'ITEM NAME',
item_sku AS ' ITEM SKU', 
SUM(quant) AS 'QUANT',
(SUM(quant*item_landing)/sum(quant)) AS 'ITEM LANDING'
FROM items
GROUP BY item_name;

SQL FIDDLE

edited Sep 18 '14 at 05:32

answered Jul 29 '14 at 08:09

Ullas

11,450
4
33
50

Depending on the sql type which is used, you need to add `item_sku` in the `GROUP BY` part as well – schlonzo Jul 29 '14 at 08:13
@schlonzo:if using `MS SQL` `item-sku` should be there in the `GROUP BY`. – Ullas Jul 29 '14 at 08:18

Calculate Weighted Average in sql for duplicate items

2 Answers2

QUERY

SQL FIDDLE

Linked