PostgreSQL AVG Aggregate Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL AVG aggregate function to calculate the average value of a set.

Getting Started with the PostgreSQL AVG aggregate function #

In PostgreSQL, the AVG aggregate function takes a set of numbers and returns their average.

Here’s the syntax for using the AVG aggregate function with a table column:

SELECT
  AVG(column1)
FROM
  table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the AVG function takes all values from column1 and returns the average.

Note: The AVG function ignores NULL from its calculation. Additionally, if column1 has no value, the AVG function returns NULL.

Finding averages for groups #

To find averages for groups, you can use the AVG function with the GROUP BY clause:

SELECT
  column2,
  AVG(column1)
FROM
  table_name
GROUP BY
  column2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• The GROUP BY clause groups rows by the values in column2 into groups.
• The AVG calculates the average value for each group.

PostgreSQL AVG Aggregate Function Examples #

Let’s explore examples of using the AVG function with the inventories table.

Finding average inventory quantity #

The following query uses the AVG function to find the average quantity of all products in the inventory:

SELECT
  AVG(quantity)
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

         avg
----------------------
 214.4000000000000000Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To get the average value in an integer, you can cast the result of the AVG function to an integer using the cast operator ::

SELECT
  AVG(quantity)::int
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 avg
-----
 214Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finding average inventory quantity by warehouses #

The following example uses the AVG() function to find the average quantity in each warehouse:

SELECT
  warehouse_id,
  AVG(quantity)::int
FROM
  inventories
GROUP BY
  warehouse_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id | avg
--------------+-----
            3 | 223
            2 | 211
            1 | 210Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

• First, the GROUP BY clause groups the rows in the inventories table by values in the warehouse_id column.
• Second, the AVG function calculates the average quantity for each warehouse.

If you want to get the warehouse name instead of id, you can join the inventories table with the warehouses table:

SELECT
  warehouse_name,
  AVG(quantity)::int
FROM
  inventories
  JOIN warehouses USING (warehouse_id)
GROUP BY
  warehouse_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     warehouse_name      | avg
-------------------------+-----
 San Francisco Warehouse | 211
 Los Angeles Warehouse   | 223
 San Jose Warehouse      | 210Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the PostgreSQL Aggregate Function in HAVING clause #

The following statement uses the AVG aggregate function in the HAVING clause to find the warehouses with an average inventory quantity greater than 210:

SELECT
  warehouse_name,
  AVG(quantity)::int
FROM
  inventories
  JOIN warehouses USING (warehouse_id)
GROUP BY
  warehouse_name
HAVING
  AVG(quantity) > 210;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     warehouse_name      | avg
-------------------------+-----
 San Francisco Warehouse | 211
 Los Angeles Warehouse   | 223Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the AVG function to calculate the average value of a set of values.

Quiz #