PostgreSQL avg function

The PostgreSQL avg function returns the average value of the expression.

Syntax of avg function in PostgreSQL

SELECT avg(aggregate_expression)
FROM tables
[WHERE conditions];

Or the syntax of the avg function when grouping results into one or more columns:

SELECT expression1,2,..._n,
avg(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1,2,..._n;

Parameters and arguments of the function

• expression1, expression2,… expression_n – Expressions that are not enclosed in the function avg and must be included in the GROUP BY operator at the end of the SQL query.
• expression_expression – This is a column or expression to be averaged.
• tables – These are the tables from which you want to get the records. At least one table must be specified in the FROM operator.
• WHERE conditions – Optional. These are the conditions that must be met to select records.

The avg function can be used in the following PostgreSQL versions

PostgreSQL 11, PostgreSQL 10, PostgreSQL 9.6, PostgreSQL 9.5, PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4.

Single Expression Example

Consider some examples of avg functions to understand how to use avg in PostgreSQL.
For example, you might be wondering what the average number of fields in an inventory table is.

SELECT avg(quantity) AS "Average Quantity"
FROM inventory;

In this example of the avg function, we used the alias avg(quantity) as “Average Quantity”. As a result, “Average Quantity” will appear as the field name when the result set returns.

Example using DISTINCT

You can use the DISTINCT operator in the avg function. For example, the SQL statement below returns the average of a unique quantity, where product_type is ‘Hardware’.

SELECT avg(DISTINCT quantity) AS "Average Quantity"
FROM inventory
WHERE product_type = 'Hardware';

If there were two quantities equal to 25, only one of these values would be used in the avg function.

Using formula

The expression contained in the avg function does not necessarily have to be a single field. You can also use a formula. For example, you may want to use the “Average Commission”.

SELECT avg(sales * 0.10) AS "Average Commission"
FROM orders;

Example using GROUP BY

You can also use the avg function to return the department and “Average Quantity” (the average number in the corresponding department). For example,

SELECT department, avg(quantity) AS "Average Quantity"
FROM inventory
GROUP BY department;

Since your SELECT operator has one column that is not encapsulated in the avg function, you should use the GROUP BY operator. Therefore, the department field should be specified in the GROUP BY section.

SQL Queries on PostgreSQL: Writing query for AVG Function