Populate Average Value of the Column in PostgreSQL Table

To populate the average value of a column in a PostgreSQL table, you can use the AVG() function along with  windows function over all_rows;  or a round about method of AVG() function in combination with an UPDATE statement. Let’s look at both approach with examples

We will be using below “products” Table

Method 1: Populate the average value of a column in a PostgreSQL with AVG and Windows Function:

In the below example we will be populating average value of a column throughout the column using AVG() and windows function

 

select *, AVG(price) over all_rows as avg_price
from products
window all_rows as ();

• AVG(price): This is a window function that calculates the average value of the price column.
• OVER all_rows: This specifies that the window function should use the window named all_rows.
• WINDOW all_rows AS ():This defines a window named all_rows. The parentheses () indicate that the window includes all rows in the result set, meaning no specific partitioning or ordering is applied, so it operates over the entire dataset.

 Output:

Method 2: Populate the average value of a column in a PostgreSQL with AVG and update statement:

To populate the average value of a column in a PostgreSQL table, you can use the AVG() function in combination with an UPDATE statement. This operation might be needed when you want to fill a column with the average value of another column

Step1 :  Add A column named avg_price

 

ALTER TABLE products ADD COLUMN avg_price decimal(10,2)

Step2 :  Calculate the Average value of the column, Update the table with the average value using SET keyword

 

UPDATE products
SET avg_price = (SELECT AVG(price) FROM products);

Now, the average value  is  updated to the avg_price column for all rows in the table

Step3 :  Verify the update

To ensure that the avg_price column has been populated correctly, select all columns from the table:

 

select * from products

So, our final table will be

Output: