PostgreSQL avg() Function

The PostgreSQL avg() function is an aggregate function that computes the average of all specified values ​​in a group and returns it.

avg() Syntax

Here is the syntax of the PostgreSQL avg() function:

avg(expr)

Typically, we use the avg() function like:

SELECT avg(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. A column name or expression that computes the average.

Return value

The PostgreSQL avg() function returns the average of all specified values ​​in a group.

avg() Examples

To demonstrate usages of the PostgreSQL avg() function, we simulate a temporary table using the following statement with UNION and SELECT:

SELECT 'Tim' name, 'Math' subject, 8 grade
UNION
SELECT 'Tim' name, 'English' subject, 9 grade
UNION
SELECT 'Tom' name, 'Math' subject, 7 grade
UNION
SELECT 'Tom' name, 'English' subject, 5 grade;

 name | subject | grade
------+---------+-------
 Tim  | English |     9
 Tom  | Math    |     7
 Tim  | Math    |     8
 Tom  | English |     5
(4 rows)

Here, we have some rows for student grades. The name column is the name of the student, the subject column is the name of the subject, and the grade column is the grade of a subject.

If you want to get the average grade for all students, use the following statement with the avg() function:

SELECT
  avg(t.grade) average_grade
FROM
  (
    SELECT 'Tim' name, 'Math' subject, 8 grade
    UNION
    SELECT 'Tim' name, 'English' subject, 9 grade
    UNION
    SELECT 'Tom' name, 'Math' subject, 7 grade
    UNION
    SELECT 'Tom' name, 'English' subject, 5 grade
  ) t;

   average_grade
--------------------
 7.2500000000000000
(1 row)

If you want to get the average grade for each student, use the following statement with the avg() function:

SELECT
  t.name,
  avg(t.grade) average_grade
FROM
  (
    SELECT 'Tim' name, 'Math' subject, 8 grade
    UNION
    SELECT 'Tim' name, 'English' subject, 9 grade
    UNION
    SELECT 'Tom' name, 'Math' subject, 7 grade
    UNION
    SELECT 'Tom' name, 'English' subject, 5 grade
  ) t
GROUP BY t.name;

 name |   average_grade
------+--------------------
 Tim  | 8.5000000000000000
 Tom  | 6.0000000000000000
(2 rows)

Here, we use the GROUP BY t.name clause to group all rows by student’s name, and use the avg(t.grade) function to average the grades in each group.