Home » PostgreSQL Window Functions » PostgreSQL AVG Window Function

PostgreSQL AVG Window Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL AVG() window function to calculate the average value in a set of values within a partition.

Getting Started with the PostgreSQL AVG() Window Function #

In PostgreSQL, the AVG() window function returns the average value in a set of values within a partition.

Here’s the syntax of the AVG() window function:

AVG(value) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

  • value is a column or expression for which you want to calculate the average value.
  • The OVER clause defines the window frame where the AVG() function applies.
  • PARTITION BY divides the result set into partitions by the partition_expression. The partition_expression can be one or more columns. If you omit the PARTITION BY clause, the AVG() function treats the whole result set as a single partition.
  • The ORDER BY clause determines the order of rows within each partition.

PostgreSQL AVG Window Function Examples #

Let’s take a look at some examples of using the AVG() window function with the products table:

PostgreSQL AVG Window Function - products Table

Finding the Average Gross Weight of All Products #

The following SELECT statement uses the AVG() window function to retrieve the average weight along with the product information of the current row:

SELECT
  product_name,
  gross_weight,
  AVG(gross_weight) OVER () AS average_weight
FROM
  products
ORDER BY
  gross_weight;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | gross_weight |   average_weight
----------------------------+--------------+---------------------
 Apple AirPods Pro 3        |         0.01 | 10.2492000000000000
 Samsung Galaxy Buds Pro 2  |         0.01 | 10.2492000000000000
 Samsung Galaxy Watch 6     |         0.07 | 10.2492000000000000
 Apple Watch Series 9       |         0.09 | 10.2492000000000000
 Samsung Galaxy S24         |         0.37 | 10.2492000000000000
 Apple iPhone 15            |         0.38 | 10.2492000000000000
 Sony Xperia 1 VI           |         0.42 | 10.2492000000000000
 Xiaomi Mi 14               |         0.43 | 10.2492000000000000
 Huawei Mate 60             |         0.46 | 10.2492000000000000
 Apple iPhone 15 Pro Max    |         0.49 | 10.2492000000000000
 Samsung Galaxy Z Fold 5    |         0.56 | 10.2492000000000000
 Samsung Galaxy Tab S9      |         1.10 | 10.2492000000000000
 Apple iPad Pro 12.9        |         1.42 | 10.2492000000000000
 Lenovo ThinkPad X1 Carbon  |         2.49 | 10.2492000000000000
 HP Spectre x360            |         3.09 | 10.2492000000000000
 Microsoft Surface Laptop 5 |         3.44 | 10.2492000000000000
 Dell XPS 15                |         3.97 | 10.2492000000000000
 Bose SoundLink Max         |         6.61 | 10.2492000000000000
 Apple iMac 24"             |         9.92 | 10.2492000000000000
 Dell Inspiron 27           |        17.64 | 10.2492000000000000
 Sony HT-A7000 Soundbar     |        18.08 | 10.2492000000000000
 LG OLED TV C3              |        39.68 | 10.2492000000000000
 Sony Bravia XR A95K        |        44.09 | 10.2492000000000000
 LG G3 OLED                 |        48.50 | 10.2492000000000000
 Samsung QN900C Neo QLED    |        52.91 | 10.2492000000000000Code language: plaintext (plaintext)

In this example, the AVG() function treats the whole result set as a single partition because of the absence of a PARTITION BY clause, and it returns the average weight of all products along with the name and weight of the current product.

To make the average weight more readable, you can cast it to a number of type DEC(5,2):

SELECT
  product_name,
  gross_weight,
  AVG(gross_weight) OVER () :: DEC(5,2) AS average_weight
FROM
  products
ORDER BY
  gross_weight;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | gross_weight | average_weight
----------------------------+--------------+----------------
 Apple AirPods Pro 3        |         0.01 |          10.25
 Samsung Galaxy Buds Pro 2  |         0.01 |          10.25
 Samsung Galaxy Watch 6     |         0.07 |          10.25
 Apple Watch Series 9       |         0.09 |          10.25
 Samsung Galaxy S24         |         0.37 |          10.25
 Apple iPhone 15            |         0.38 |          10.25
 Sony Xperia 1 VI           |         0.42 |          10.25
 Xiaomi Mi 14               |         0.43 |          10.25
 Huawei Mate 60             |         0.46 |          10.25
 Apple iPhone 15 Pro Max    |         0.49 |          10.25
 Samsung Galaxy Z Fold 5    |         0.56 |          10.25
 Samsung Galaxy Tab S9      |         1.10 |          10.25
 Apple iPad Pro 12.9        |         1.42 |          10.25
 Lenovo ThinkPad X1 Carbon  |         2.49 |          10.25
 HP Spectre x360            |         3.09 |          10.25
 Microsoft Surface Laptop 5 |         3.44 |          10.25
 Dell XPS 15                |         3.97 |          10.25
 Bose SoundLink Max         |         6.61 |          10.25
 Apple iMac 24"             |         9.92 |          10.25
 Dell Inspiron 27           |        17.64 |          10.25
 Sony HT-A7000 Soundbar     |        18.08 |          10.25
 LG OLED TV C3              |        39.68 |          10.25
 Sony Bravia XR A95K        |        44.09 |          10.25
 LG G3 OLED                 |        48.50 |          10.25
 Samsung QN900C Neo QLED    |        52.91 |          10.25Code language: plaintext (plaintext)

Finding the Average Gross Weight of Products within Each Category #

The following SELECT statement uses the AVG() window function to retrieve the average gross weight of products within each category:

SELECT
  category_id,
  product_name,
  gross_weight,
  AVG(gross_weight) OVER (
    PARTITION BY category_id
  )::DEC(5,2) AS average_weight
FROM
  products
ORDER BY
  category_id,
  gross_weight;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_id |        product_name        | gross_weight | average_weight
-------------+----------------------------+--------------+----------------
           3 | Samsung Galaxy S24         |         0.37 |           0.44
           3 | Apple iPhone 15            |         0.38 |           0.44
           3 | Sony Xperia 1 VI           |         0.42 |           0.44
           3 | Xiaomi Mi 14               |         0.43 |           0.44
           3 | Huawei Mate 60             |         0.46 |           0.44
           3 | Apple iPhone 15 Pro Max    |         0.49 |           0.44
           3 | Samsung Galaxy Z Fold 5    |         0.56 |           0.44
           4 | Samsung Galaxy Tab S9      |         1.10 |           1.26
           4 | Apple iPad Pro 12.9        |         1.42 |           1.26
           5 | Apple AirPods Pro 3        |         0.01 |           0.01
           5 | Samsung Galaxy Buds Pro 2  |         0.01 |           0.01
           6 | Samsung Galaxy Watch 6     |         0.07 |           0.08
           6 | Apple Watch Series 9       |         0.09 |           0.08
           8 | LG OLED TV C3              |        39.68 |          46.30
           8 | Sony Bravia XR A95K        |        44.09 |          46.30
           8 | LG G3 OLED                 |        48.50 |          46.30
           8 | Samsung QN900C Neo QLED    |        52.91 |          46.30
           9 | Bose SoundLink Max         |         6.61 |          12.35
           9 | Sony HT-A7000 Soundbar     |        18.08 |          12.35
          11 | Lenovo ThinkPad X1 Carbon  |         2.49 |           3.25
          11 | HP Spectre x360            |         3.09 |           3.25
          11 | Microsoft Surface Laptop 5 |         3.44 |           3.25
          11 | Dell XPS 15                |         3.97 |           3.25
          12 | Apple iMac 24"             |         9.92 |          13.78
          12 | Dell Inspiron 27           |        17.64 |          13.78Code language: plaintext (plaintext)

How it works:

  1. The PARTITION BY clause divides the rows by category_id into partitions (or categories).
  2. The AVG() window function returns the average gross weight within each category.

Using AVG Window Function with Join #

To retrieve the category names, you can join the products table with the categories table:

Using PostgreSQL AVG Window Function with JOIN
SELECT
  category_name,
  product_name,
  gross_weight,
  AVG(gross_weight) OVER (
    PARTITION BY category_id
  ):: DEC(5,2) AS average_weight
FROM
  products
  JOIN categories USING (category_id)
ORDER BY
  category_name,
  gross_weight;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_name |        product_name        | gross_weight | average_weight
---------------+----------------------------+--------------+----------------
 Accessories   | Samsung Galaxy Buds Pro 2  |         0.01 |           0.01
 Accessories   | Apple AirPods Pro 3        |         0.01 |           0.01
 Audio Systems | Bose SoundLink Max         |         6.61 |          12.35
 Audio Systems | Sony HT-A7000 Soundbar     |        18.08 |          12.35
 Desktops      | Apple iMac 24"             |         9.92 |          13.78
 Desktops      | Dell Inspiron 27           |        17.64 |          13.78
 Laptops       | Lenovo ThinkPad X1 Carbon  |         2.49 |           3.25
 Laptops       | HP Spectre x360            |         3.09 |           3.25
 Laptops       | Microsoft Surface Laptop 5 |         3.44 |           3.25
 Laptops       | Dell XPS 15                |         3.97 |           3.25
 Smartphones   | Samsung Galaxy S24         |         0.37 |           0.44
 Smartphones   | Apple iPhone 15            |         0.38 |           0.44
 Smartphones   | Sony Xperia 1 VI           |         0.42 |           0.44
 Smartphones   | Xiaomi Mi 14               |         0.43 |           0.44
 Smartphones   | Huawei Mate 60             |         0.46 |           0.44
 Smartphones   | Apple iPhone 15 Pro Max    |         0.49 |           0.44
 Smartphones   | Samsung Galaxy Z Fold 5    |         0.56 |           0.44
 Tablets       | Samsung Galaxy Tab S9      |         1.10 |           1.26
 Tablets       | Apple iPad Pro 12.9        |         1.42 |           1.26
 Televisions   | LG OLED TV C3              |        39.68 |          46.30
 Televisions   | Sony Bravia XR A95K        |        44.09 |          46.30
 Televisions   | LG G3 OLED                 |        48.50 |          46.30
 Televisions   | Samsung QN900C Neo QLED    |        52.91 |          46.30
 Wearables     | Samsung Galaxy Watch 6     |         0.07 |           0.08
 Wearables     | Apple Watch Series 9       |         0.09 |           0.08Code language: plaintext (plaintext)

Summary #

  • Use the AVG() window function to retrieve the average value in a set of values within a partition.

Quiz #

Was this tutorial helpful ?
PostgreSQL MAX Window Function
Previously
PostgreSQL MAX Window Function
Up Next
PostgreSQL COUNT Window Function
PostgreSQL COUNT Window Function