Postgresql Average + Examples

In this PostgreSQL tutorial, we are going to cover the “Postgresql average” and cover the following topics.

• Postgresql average
• Postgresql average count
• Postgresql average group by
• Postgresql average multiple columns
• Postgresql average precision
• Postgresql average query time
• Postgresql average date
• Postgresql average row size

Postgresql average

There is an AVG() function in Postgresql to calculate the average value of a set. Average is the sum of all values divided by the number of values in a set.

The AVG() function is also called the aggregate function.

The syntax of the AVG() function is given below.

AVG(column_name);

Let’s create a table fruit_sell.

CREATE TABLE fruit_sell(id serial,f_name varchar,
		  f_fixed_price float,f_sold_price 
		 float,sold_date timestamp);

Insert the following records.

INSERT INTO fruit_sell(f_name,f_fixed_price,f_sold_price,sold_date)VALUES('Mango',2,3,'2017-01-01 00:12:00'),
('Mango',2,4,'2017-01-01 10:50:00'),('Banana',3,2,'2017-01-01 03:12:00'),('Banana',4,3.5,'2017-01-01 01:12:00'),
('Mango',4,3,'2017-01-02 09:12:00'),('Mango',4.5,4,'2017-01-02 10:27:00'),('Banana',3.3,4,'2017-01-02 08:12:00'),
('Banana',2,3.5,'2017-01-02 23:12:00'),('Apple',5,7,'2017-01-03 00:30:00'),('Apple',6,5.5,'2017-01-03 24:00:00'),
('Grapes',3,2,'2017-01-03 15:12:00'),('Grapes',4,3.5,'2017-01-03 02:30:00'),('Apple',4,3,'2017-01-04 09:12:00'),
('Apple',4.5,4,'2017-01-04 08:27:00'),('Grapes',3.3,4,'2017-01-04 14:12:00'),('Grapes',2,3.5,'2017-01-04 20:25:00');

Now, calculate the average f_fixed_price of each fruit.

SELECT avg(f_fixed_price) FROM fruit_sell;

The output of the code is given below.

The average fixed price of each fruit is 3.53 dollars.

Let’s again understand with different examples by using the new table name major_cities that contain the population of major cities in the United Kindom.

The table is shown below.

The above table shows the population of each major city in the United Kindom. Now we will calculate the average population of major cities in the United Kindom using the below query.

SELECT avg(population) as avg_population FROM major_cities;

The code shows that the average population of major cities in the United Kindom is around 1467123.3.

Read: Postgresql if else

Postgresql average count

In Postgresql, first, we will count the number of values using the COUNT(), then we will calculate the average.

Now count the number of values in column f_sold_price and sum the f_sold_price column values using the sum function. Then divide the sum value by the number of values.

SELECT (sum(f_sold_price)/count(f_sold_price)) FROM fruit_sell;

In the above code, first, we are calculating the sum of all values in a column using the sum(f_sold_price), then dividing the sum result by the number of values in a column f_sold_price using count(f_sold_price).

The output of the above code is given below.

Read: Postgres date range

Postgresql average group by

In Postgresql, we can group the category or rows using the GROUP BY clause.

Run the below code to find the average sold price of each fruit using the GROUP BY clause.

SELECT f_name, avg(f_sold_price) FROM fruit_sell
GROUP BY f_name;

The output of the above code is given below.

Read: Drop Database PSQL

Postgresql average multiple columns

In Postgresql, the average function can calculate the average or mean value of multiple columns at the same time.

Here, we are going to calculate the average price of columns f_fixed_price and f_sold_column.

SELECT avg(f_fixed_price),avg(f_sold_price) FROM fruit_sell;

In the above output, we have calculated the average price of each fruit of both columns f_fixed_price and f_sold_price.

Read: PostgreSQL Update

Postgresql average precision

In Postgresql, the average function can also be applied to the float, if the float value has a large number of decimal places or precision, then we can reduce this precision using the round function.

The syntax of the round function is given below.

round(val::numeric,2)

Let’s calculate the average sold price of each fruit before reducing the precision.

SELECT avg(f_sold_price) FROM fruit_sell;

From the above output, the average price of each fruit is 3.71875 with 5 precision means the number of digits after the decimal is 5.

Now run the below code to reduce the precision to 2 digits using the round function.

SELECT round(avg(f_sold_price)::numeric,2) FROM fruit_sell;

As we can see in the above output, the average price of each fruit precision is reduced to 2 digits.

Read: Postgresql date comparison

Postgresql average query time

In Postgresql, we can find the query time of any statement or function used in any query.

To see the runtime or query time of the average function used in the statement, run the below command.

EXPLAIN ANALYZE SELECT AVG(f_sold_price) FROM fruit_sell;

The output of the above code is given below.

Read: Postgresql date to string

Postgresql average date

In Postgresql, the average function can be used to find the average of dates, which means if we have columns that contain the date, then we can find the average of dates.

Let’s find the average of sold_date column of the fruit_sell table.

SELECT to_timestamp(avg(extract(epoch from sold_date)))::date AS avg_sold_date
FROM fruit_sell; 

The output of the above code is given below.

In the above output, we can see that the average date of sold fruit is 2017-01-03.

Read: Postgresql escape single quote

Postgresql average row size

In Postgresql, we can find the row size of each row using the pg_column_size catalog that calculates the size of rows.

Let’s find the average row size using the below code.

SELECT avg(pg_column_size(t.*)) as filesize, count(*) as filerow FROM fruit_sell as t;

The output of the above code is given below.

You may also like to read the following articles on PostgreSQL.

• PostgreSQL list users
• Postgresql now() function
• PostgreSQL Date Difference
• PostgreSQL list databases
• PostgreSQL Data Types
• PostgreSQL Date Add

So, in this tutorial, we have learned about “Postgresql average” and covered the following topics.

• Postgresql average
• Postgresql average count
• Postgresql average group by
• Postgresql average multiple columns
• Postgresql average precision
• Postgresql average query time
• Postgresql average date
• Postgresql average row size