WAVG
Updated 2024-03-07 15:47:57.893000
Syntax
SELECT [westclintech].[wct].[WAVG] (<@wght, float,>
<@val, float,>)
Description
Use the aggregate WAVG to calculate the weighted average. Given a set of data, x, and a set of weights, w, the weighted average is calculated as:
\bar{x}=\frac{\Sigma_{i=1}^nw_ix_i}{\Sigma_{i=1}^nw_i}
Arguments
@ val
the values to be used in the WAVG calculation. @val is an expression of type float or of a type that can be implicitly converted to float.
@ wght
the weights to be used in the WAVG calculation. @wght is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
WAVG is an aggregate function and follows the same conventions as all other AGGREGATE functions in SQL Server.
If the sum of the weights is zero, then a NULL is returned.
Examples
In this example we have created a transaction table consisting of a symbol, a quantity, and a price. Quantity values less than zero indicate sales. This SQL loads up the table, which we will use in the next few examples.
SELECT *
INTO #a
FROM
(
VALUES
('GHI', 5000, 9.2),
('MNO', -750, 10.3),
('MNO', -750, 10.5),
('MNO', 4000, 11.4),
('DEF', 5000, 9.2),
('JKL', 4000, 9.5),
('DEF', 1000, 10.6),
('JKL', 6000, 9.9),
('JKL', -1750, 11.4),
('GHI', -2750, 10.7),
('GHI', 2000, 10.6),
('GHI', 3000, 10),
('MNO', 1000, 10),
('GHI', -1750, 10.6),
('MNO', 6000, 9.3),
('ABC', 1000, 10.9),
('MNO', 5000, 11.1),
('JKL', 3000, 10.7),
('MNO', 6000, 11.3),
('GHI', 4000, 9.9),
('DEF', 1000, 10.3),
('ABC', -1750, 9.5),
('MNO', -3750, 9.7),
('GHI', -2750, 11.4),
('DEF', 4000, 9.6),
('DEF', 3000, 9),
('DEF', -3750, 11.3),
('JKL', 1000, 9.4),
('MNO', -3750, 10.5),
('JKL', 3000, 9.8),
('MNO', 2000, 9.5),
('ABC', -1750, 9.9),
('DEF', 6000, 9.5),
('JKL', -750, 10.3),
('JKL', 2000, 9.9),
('DEF', -750, 10.7),
('ABC', -3750, 10.3),
('MNO', -3750, 9.9),
('ABC', 4000, 10.9),
('GHI', 1000, 10.1),
('JKL', 2000, 11.1),
('JKL', 2000, 9.4),
('DEF', 5000, 11.2),
('ABC', 4000, 10.3),
('GHI', 6000, 11.4),
('MNO', 1000, 11),
('JKL', 5000, 11.2),
('JKL', -1750, 10.6),
('ABC', 6000, 11.1),
('GHI', -1750, 11.2),
('GHI', 6000, 9.4),
('DEF', 3000, 10.8),
('JKL', 4000, 10.8),
('DEF', -750, 10.4),
('MNO', -1750, 10.9),
('ABC', -1750, 9.1),
('ABC', 5000, 11.1),
('GHI', 5000, 9.7),
('GHI', 5000, 9.1),
('GHI', 4000, 10.7),
('ABC', -1750, 9.7),
('DEF', 1000, 10.1),
('ABC', 2000, 11.2),
('DEF', 4000, 10.6),
('ABC', 2000, 9.3),
('GHI', -2750, 10.6),
('GHI', -3750, 9.1),
('DEF', 3000, 11.3),
('GHI', -3750, 10.6),
('DEF', 3000, 11.1),
('DEF', 5000, 11.3),
('JKL', 5000, 9.4),
('DEF', -750, 9),
('ABC', -1750, 11.4),
('MNO', -750, 9.4),
('MNO', 2000, 9.5),
('JKL', 2000, 10.7),
('MNO', 1000, 11),
('MNO', 6000, 9.2),
('ABC', 6000, 11.2),
('MNO', 5000, 11),
('ABC', 1000, 9.9),
('JKL', 2000, 9.6),
('DEF', -2750, 9.4),
('DEF', 4000, 9.2),
('JKL', -1750, 11.2),
('MNO', 2000, 9.4),
('JKL', 3000, 11.4),
('DEF', -3750, 9.1),
('ABC', -2750, 10.9),
('ABC', -2750, 9.1),
('GHI', -1750, 11),
('MNO', 1000, 11.4),
('MNO', 1000, 11.2),
('MNO', 1000, 11.1),
('DEF', 5000, 9.4),
('DEF', 4000, 9.7),
('MNO', -3750, 10.5),
('ABC', 2000, 10.1),
('GHI', -2750, 10.1)
) n (sym, qty, pr);
In this first example, we will calculate the weighted average price of the purchases (which have quantity > 0).
SELECT a.sym,
SUM(a.qty) as Purchases,
wct.WAVG(a.qty, a.pr) as Avg_price_purch
from #a a
WHERE a.qty > 0
GROUP BY a.sym
ORDER BY 1;
This produces the following result.
{"columns":[{"field":"sym"},{"field":"Purchases","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Avg_price_purch","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"sym":"ABC","Purchases":"33000","Avg_price_purch":"10.7909090909091"},{"sym":"DEF","Purchases":"57000","Avg_price_purch":"10.1140350877193"},{"sym":"GHI","Purchases":"41000","Avg_price_purch":"9.96341463414634"},{"sym":"JKL","Purchases":"44000","Avg_price_purch":"10.2295454545455"},{"sym":"MNO","Purchases":"44000","Avg_price_purch":"10.3954545454545"}]}
In this example, we will add two columns to the resultant table from the first example to calculate the sales quantity and the weighted average price of the sales.
SELECT a.sym,
SUM(a.qty) as Purchases,
wct.WAVG(a.qty, a.pr) as Avg_price_purch,
SUM(b.qty) as Sales,
wct.WAVG(b.qty, b.pr) as Avg_price_sales
from #a a,
#a b
WHERE a.qty > 0
AND b.qty < 0
AND a.sym = b.sym
GROUP BY a.sym
ORDER BY 1;
This produces the following result.
{"columns":[{"field":"sym"},{"field":"Purchases","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Avg_price_purch","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Avg_price_sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"sym":"ABC","Purchases":"264000","Avg_price_purch":"10.7909090909091","Sales":"-180000","Avg_price_sales":"10.0236111111111"},{"sym":"DEF","Purchases":"342000","Avg_price_purch":"10.1140350877193","Sales":"-200000","Avg_price_sales":"9.994"},{"sym":"GHI","Purchases":"369000","Avg_price_purch":"9.96341463414634","Sales":"-237500","Avg_price_sales":"10.4831578947368"},{"sym":"JKL","Purchases":"176000","Avg_price_purch":"10.2295454545455","Sales":"-84000","Avg_price_sales":"10.9708333333333"},{"sym":"MNO","Purchases":"352000","Avg_price_purch":"10.3954545454545","Sales":"-285000","Avg_price_sales":"10.2092105263158"}]}
In this example we will add two more columns to the resultant table, which will be the inventory balance, which is the excess of the purchases over the sales, and the value of the inventory, which is the inventory balance multiplied by the average purchase price.
SELECT *,
(Avg_price_sales - Avg_price_purch) * ABS(Sales) as Profit,
Purchases + sales as Inventory,
(Purchases + sales) * Avg_price_purch as Inventory_Cost
FROM
(
SELECT a.sym,
SUM(a.qty) as Purchases,
wct.WAVG(a.qty, a.pr) as Avg_price_purch,
SUM(b.qty) as Sales,
wct.WAVG(b.qty, b.pr) as Avg_price_sales
from #a a,
#a b
WHERE a.qty > 0
AND b.qty < 0
AND a.sym = b.sym
GROUP BY a.sym
) m
ORDER BY 1;
This produces the following result.
{"columns":[{"field":"sym"},{"field":"Purchases","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Avg_price_purch","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Avg_price_sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Profit","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Inventory","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Inventory_Cost","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"sym":"ABC","Purchases":"264000","Avg_price_purch":"10.7909090909091","Sales":"-180000","Avg_price_sales":"10.0236111111111","Profit":"-138113.636363637","Inventory":"84000","Inventory_Cost":"906436.363636364"},{"sym":"DEF","Purchases":"342000","Avg_price_purch":"10.1140350877193","Sales":"-200000","Avg_price_sales":"9.994","Profit":"-24007.0175438596","Inventory":"142000","Inventory_Cost":"1436192.98245614"},{"sym":"GHI","Purchases":"369000","Avg_price_purch":"9.96341463414634","Sales":"-237500","Avg_price_sales":"10.4831578947368","Profit":"123439.024390244","Inventory":"131500","Inventory_Cost":"1310189.02439024"},{"sym":"JKL","Purchases":"176000","Avg_price_purch":"10.2295454545455","Sales":"-84000","Avg_price_sales":"10.9708333333333","Profit":"62268.1818181817","Inventory":"92000","Inventory_Cost":"941118.181818182"},{"sym":"MNO","Purchases":"352000","Avg_price_purch":"10.3954545454545","Sales":"-285000","Avg_price_sales":"10.2092105263158","Profit":"-53079.5454545451","Inventory":"67000","Inventory_Cost":"696495.454545454"}]}
In this example, the sum of the weights is zero.
SELECT wct.WAVG(w, v) as WAVG
FROM
(
VALUES
(100, 9.5),
(200, 9.8),
(-300, 9.7)
) n (w, v);
This produces the following result.
{"columns":[{"field":"WAVG"}],"rows":[{"WAVG":"NULL"}]}
See Also
MMULTN - Matrix Mulitplication
DEMA - Daily Exponential Moving Average
DWMA - Daily Weighted Moving Average