Wighted Average in DuckDB SQL

How do I calculate weighted average in DuckDB?

The weighted average is similar to an ordinary arithmetic mean (the most common type of average), except that instead of each of the data points contributing equally to the final average, some data points contribute more than others. The notion of weighted mean plays a role in descriptive statistics and also occurs in a more general form in several other areas of mathematics.

For example in finance it is common to weight trades by the number of trades executed at a price point, to find a weighted average of all prices.

Simple Weight Average Example

Given a basket of eggs, let's find the weighted average when using the number of eggs as the weight:

Weighted Average = ((1*0.1) + (2*0.2) + (5*0.3) + (3*0.4)) / (1 + 2 + 5 + 3) = 0.2909091

DROP TABLE IF EXISTS basket;
CREATE TABLE basket (number INT,weight DECIMAL );
INSERT INTO basket (number, weight) VALUES
(1, 0.1),
(2, 0.2),
(5, 0.3),
(3, 0.4);

SELECT SUM(number*weight)/SUM(NUMBER) AS WAVG FROM basket;

wavg
0.2909091

Weight Average for Multiple Groups

To find the weighted average for distinct groups, use GROUP BY to separate the groups. For example below is shown the height data for male and female athletes. TO find the separate weighted average we would:

DROP TABLE IF EXISTS athletes;
CREATE TABLE athletes (sex VARCHAR, number INT,height DECIMAL );
INSERT INTO athletes (sex,number, height) VALUES
('M', 1, 110),
('M', 2, 120),
('M', 5, 130),
('M', 3, 140);
INSERT INTO athletes (sex,number, height) VALUES
('F', 3, 90),
('F', 1, 100),
('F', 5, 110),
('F', 2, 120);

SELECT sex,SUM(number*height)/SUM(NUMBER) AS WAVG FROM athletes GROUP BY sex;

Weighted Average for M-Males = ((1*110) + (2*120) + (5*130) + (3*140)) / (1 + 2 + 5 + 3) = 129.0909091

sex	WAVG
F	105.4545455
M	129.0909091

Wighted Average in DuckDB SQL

How do I calculate weighted average in DuckDB?

Simple Weight Average Example

Weight Average for Multiple Groups

See Also