PostgreSQL MIN Aggregate Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL MIN aggregate function to find the smallest value in a set of values.

Getting Started with the PostgreSQL MIN Aggregate Function #

In PostgreSQL, the MIN aggregate function accepts a set of values and returns the smallest one.

The MIN function is flexible and can apply to numbers, dates, and strings, allowing you to work with various data types.

Basic Syntax #

The following shows how to use the MIN aggregate function to find the smallest value in a column of a table:

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

It’s important to note that the MIN function ignores NULL when determining the minimum value.

Using MIN with GROUP BY clause #

To find the smallest values of each group, you can use the MIN function with the GROUP BY clause:

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

In this syntax:

The GROUP BY clause categorizes the values into groups by the values in column1.
The MIN function returns the smallest value in the column2 for each group.

This query comes in handy when you want to get insights from your data.

Using Expressions with MIN function #

Besides a table column, you can use an expression in the MIN function:

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

In this syntax, the expression may involve table columns with operators.

PostgreSQL MIN Aggregate Function Examples #

Let’s explore some examples of using the MIN aggregate function.

Finding the Lowest Product Price #

The following example uses the MIN function to find the lowest price of all products

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

Try it

Output:

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

In this example, the MIN function examines all values in the price column of the products table and returns the smallest one.

Finding the Product with the Lowest Price #

To find the product with the lowest price, you can use a subquery:

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

Try it

Output:

       product_name        | price
---------------------------+--------
 Samsung Galaxy Buds Pro 2 | 199.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

The subquery returns the lowest price.
The outer query finds the product whose price matches the lowest price.

The query will return more than one product if these products have the same lowest price.

Finding Products with the Lowest Prices in Each Category #

The following example uses the MIN aggregate function to find the product with the lowest price in each category:

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

Try it

Output:

 category_id | min_price
-------------+-----------
           5 |    199.99
           6 |    349.99
           9 |    399.99
           4 |    699.99
           3 |    799.99
          12 |    999.99
          11 |   1299.99
           8 |   1999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

The GROUP BY clause groups the products by the category_id.
The MIN function returns the lowest price for each group.

To retrieve the category name and lowest price, you can join the products table with the categories table:

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

Try it

Output:

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

To find the category with the lowest price of less than 500, you can use the MIN function in the HAVING clause:

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

Try it

Output:

 category_name | min_price
---------------+-----------
 Audio Systems |    399.99
 Wearables     |    349.99
 Accessories   |    199.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You cannot use the min_price column alias in the HAVING clause because PostgreSQL evaluates the HAVING clause before the SELECT clause.

However, you can use the min_price column alias in the ORDER BY clause because PostgreSQL evaluates the ORDER BY clause after the SELECT clause.

Using the PostgreSQL MIN Function with Dates #

The transactions table records the inventory transactions:

The following statement uses the MIN aggregate function to find the earliest transaction date from the transactions table:

SELECT
  MIN(transaction_date)
FROM
  transactions;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    min
------------
 2024-12-01Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To find the earliest transaction in each warehouse, you can use the GROUP BY clause:

SELECT
  warehouse_id,
  MIN(transaction_date) AS earliest_transaction_date
FROM
  transactions
GROUP BY
  warehouse_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id | earliest_transaction_date
--------------+---------------------------
            3 | 2024-12-01
            2 | 2024-12-01
            1 | 2024-12-01Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the PostgreSQL MIN Function with Expressions #

The following example uses the MIN function to find the lowest inventory amount of all products:

SELECT
  MIN(quantity * price) AS min_inventory_amount
FROM
  products
JOIN inventories USING (product_id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 min_inventory_amount
----------------------
             37998.10Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we multiply the quantity in the inventory by the product price and use the MIN function to find the lowest inventory amount.

To find the lowest inventory amount by warehouse, you can group the inventory by warehouses:

SELECT  
  warehouse_id, 
  MIN(quantity * price) AS min_inventory_amount
FROM 
  products
JOIN inventories USING (product_id)
GROUP BY 
  warehouse_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id | min_inventory_amount
--------------+----------------------
            3 |             79998.00
            2 |             37998.10
            1 |             44998.20Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

Use the MIN function to find the smallest value in a set of values.
Combine MIN with other SQL clauses like GROUP BY, HAVING, and JOIN to extract meaningful insights from your data.

Quiz #

Was this tutorial helpful ?