Often in PostgreSQL you may want to calculate a weighted average using two columns in a table.
You can use the following basic syntax to do so:
SELECT SUM(amount*price) / SUM(amount) AS weighted_avg_price
FROM sales;
This particular example will calculate the weighted average of the values in the price column of the sales table, using the amount column as the weights.
The following example shows how to use this syntax in practice.
Example: How to Calculate a Weighted Average in PostgreSQL
Suppose that we create the following table named sales that contains information about various ssales made at two different retail stores:
-- create table named 'sales'
CREATE TABLE sales (
store TEXT,
price NUMERIC,
amount NUMERIC
);
-- add rows to the table
INSERT INTO sales VALUES ('A', 8, 1);
INSERT INTO sales VALUES ('A', 5, 3);
INSERT INTO sales VALUES ('A', 6, 2);
INSERT INTO sales VALUES ('B', 7, 2);
INSERT INTO sales VALUES ('B', 12, 5);
INSERT INTO sales VALUES ('B', 14, 4);
-- view all rows in table
SELECT * FROM sales;
store | price | amount
-------+-------+--------
A | 8 | 1
A | 5 | 3
A | 6 | 2
B | 7 | 2
B | 12 | 5
B | 14 | 4
Suppose that we would like to calculate the weighted average of the values in the price column, using the values in the amount column as the weights.
We can use the following syntax to do so:
SELECT SUM(amount*price) / SUM(amount) AS weighted_avg_price
FROM sales;
weighted_avg_price
---------------------
9.7058823529411765
From the output we can see that the weighted average of values in the price column is about 9.7059.
Note that we could also use the GROUP BY statement to calculate the weighted average of values in the price column, grouped by the values in the store column:
SELECT store,
SUM(amount*price) / SUM(amount) AS weighted_avg_price
FROM sales
GROUP BY store;
store | weighted_avg_price
-------+---------------------
B | 11.8181818181818182
A | 5.8333333333333333
The output displays the weighted average of values in the price column, grouped by values in the store column.
You can use similar syntax to calculate a weighted average in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Calculate Average by Group
PostgreSQL: How to Calculate Average with Condition
PostgreSQL: How to Calculate Average Across Multiple Columns
