PostgreSQL Aggregate Functions

Summary: in this tutorial, you’ll learn about PostgreSQL aggregate functions including AVG, COUNT, MAX, MIN, and SUM.

Introduction to PostgreSQL Aggregate Functions #

Aggregate functions in PostgreSQL are remarkably simple. They take a set of values and return a single value, making your data analysis tasks a breeze.

PostgreSQL supports standard aggregate functions:

The MIN function returns the minimum value in a set of values.
The MAX function returns the maximum value in a set of values.
The SUM function returns the total of values.
The AVG function returns the average of values.
The COUNT function returns the number of values in a set.

Basic Syntax #

The following shows how to use an aggregate function with a column in a table:

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

In this syntax, the aggregate_function will apply to all values in column1 from the table_name and return a single value.

Using with the GROUP BY Clause #

When using the GROUP BY clause with aggregate functions, you can calculate summarized values of groups to get a deeper insight into your data:

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

In this syntax:

The GROUP BY clause groups values in column2 into groups.
The aggregate_function calculates a single value from each group’s values in column1.

Using in HAVING Clause #

The HAVING clause, when used with an aggregate function, gives you the flexibility to filter groups by the aggregated value, putting you in complete control of your data analysis:

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

PostgreSQL Aggregate Function Examples #

We’ll take the products table from the inventory database to demonstrate the aggregate functions.

The products table contains information about various products such as their names, prices, brands, categories, and safety stocks.

COUNT Function #

The following example uses the COUNT function to return the number of products in the products table:

SELECT
  COUNT(product_id)
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 count
-------
    25Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To find the number of products for each brand, you group the products by brand_id and count the number of products in each:

SELECT
  brand_id,
  COUNT(product_id) AS product_count
FROM
  products
GROUP BY
  brand_id
ORDER BY
  product_count DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_id | product_count
----------+---------------
        2 |             6
        1 |             6
        7 |             4
        5 |             3
        6 |             1
        9 |             1
        8 |             1
        3 |             1
        4 |             1
       10 |             1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you want to return the brand name instead of the id, you can join the products with the brands table:

SELECT
  brand_name,
  COUNT(product_id) AS product_count
FROM
  products
  JOIN brands USING (brand_id)
GROUP BY
  brand_name
ORDER BY
  product_count DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_name | product_count
------------+---------------
 Apple      |             6
 Samsung    |             6
 Dell       |             4
 Sony       |             3
 Lenovo     |             1
 Microsoft  |             1
 LG         |             1
 Xiaomi     |             1
 HP         |             1
 Huawei     |             1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

AVG Function #

The following statement uses the AVG() function to find the average price of smartphones with category_id 3:

SELECT
  AVG(price) AS average_price
FROM
  products
WHERE
  category_id = 3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     average_price
-----------------------
 1121.4185714285714286Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To round the average price to a number with two digits after the decimal point, you can use the ROUND() function:

SELECT
  ROUND(AVG(price), 2) AS average_price
FROM
  products
WHERE
  category_id = 3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output

 average_price
---------------
       1121.42Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can group the products by category and compute the average price of products in each:

SELECT
  category_name,
  ROUND(AVG(price), 2) AS average_price
FROM
  products
  JOIN categories USING (category_id)
GROUP BY
  category_name
ORDER BY
  average_price DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

category_name | average_price
---------------+---------------
 Televisions   |       2499.99
 Laptops       |       1449.99
 Desktops      |       1149.99
 Smartphones   |       1121.42
 Tablets       |        899.99
 Audio Systems |        849.99
 Wearables     |        374.99
 Accessories   |        224.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

MIN Function #

The following statement uses the MIN() function to find the lowest price of products with category_id 3:

SELECT
  MIN(price)
FROM
  products
WHERE category_id = 3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  min
--------
 799.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To find the lowest price of products in each category, you can use the GROUP BY clause to group products by category and apply the MIN function to each group:

SELECT
  category_name,
  MIN(price)
FROM
  products
  JOIN categories USING (category_id)
GROUP BY
  category_name
ORDER BY
  category_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

category_name |   min
---------------+---------
 Accessories   |  199.99
 Audio Systems |  399.99
 Desktops      |  999.99
 Laptops       | 1299.99
 Smartphones   |  799.99
 Tablets       |  699.99
 Televisions   | 1999.99
 Wearables     |  349.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

MAX Function #

The following SELECT statement uses the MAX() function to find the highest price of products with category_id 3:

SELECT
  MAX(price)
FROM
  products
WHERE
  category_id = 3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   max
---------
 1799.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To find the highest price of products in each category, you can use the GROUP BY clause to group products by category and apply the MAX function to each group:

SELECT
  category_name,
  MAX(price)
FROM
  products
  JOIN categories USING (category_id)
GROUP BY
  category_name
ORDER BY
  category_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_name |   max
---------------+---------
 Accessories   |  249.99
 Audio Systems | 1299.99
 Desktops      | 1299.99
 Laptops       | 1599.99
 Smartphones   | 1799.99
 Tablets       | 1099.99
 Televisions   | 2999.99
 Wearables     |  399.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

SUM Function #

The following SELECT statement uses the SUM function to calculate the total safety stocks for all products:

SELECT
  SUM(safety_stock)
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 sum
-----
 425Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To calculate the total safety stock for each brand, you can group the products by brand and apply the SUM function to each group:

SELECT
  brand_name,
  SUM(safety_stock) AS total_safety_stock
FROM
  products
  JOIN brands USING (brand_id)
GROUP BY
  brand_name
ORDER BY
  total_safety_stock DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_name | total_safety_stock
------------+--------------------
 Samsung    |                175
 Apple      |                150
 Sony       |                 40
 Huawei     |                 30
 Dell       |                 20
 LG         |                 10
 HP         |                  0
 Lenovo     |                  0
 Xiaomi     |                  0
 Microsoft  |                  0Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using Multiple PostgreSQL Aggregate Functions in the Same Statement #

The following statement uses all the MIN, MAX, AVG, COUNT, and SUM functions to find the min price, max price, average price, product count, and total safety stock of all products:

SELECT
  MIN(price) AS min_price,
  MAX(price) AS max_price,
  ROUND(AVG(price),2) AS average_price,
  COUNT(product_id) AS product_count,
  SUM(safety_stock) AS total_safety_stock
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 min_price | max_price | average_price | product_count | total_safety_stock
-----------+-----------+---------------+---------------+--------------------
    199.99 |   2999.99 |       1225.99 |            25 |                425Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

An aggregate function accepts a set of values and returns a single value.
PostgreSQL offers standard aggregate functions, including AVG, COUNT, MAX, MIN, and SUM.
Use an aggregate function with the GROUP BY clause to calculate an aggregated value for each group.

Was this tutorial helpful ?