SQL Server WAVG Function

Updated 2024-03-07 15:47:57.893000

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}

Syntax

SELECT [westclintech].[wct].[WAVG] (<@wght, float,>
<@val, float,>)

Arguments

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.

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.

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.

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.

See Also

MMULT - Matrix Mulitplication

MMULTN - Matrix Mulitplication

DEMA - Daily Exponential Moving Average

DWMA - Daily Weighted Moving Average

RUNNINGAVG - Calculate the average of column values in an ordered resultant table, without having to do a self-join.

MOVINGAVG - Moving Average

RUNNINGEWMA - Running Exponentially Weighted Moving Average

MOVINGEWMA - Moving Exponentially Weighted Moving Average