How to combine the MIN() function with the WHERE clause in SQL

The SQL MIN() function with WHERE clause

The aggregate functions can be used in conjunction with the WHERE clause to gain further insights from our data. One of these is the MIN() function.

In SQL, the MIN() function is used to compute the smallest or minimum value of numeric values in a column.

Syntax

Let’s view the syntax of the MIN() function.

SELECT MIN(column_name)
FROM table_name
WHERE condition;

Example

Let’s assume we have a table called Person with columns such as name, salary, state, and gender.

Now, we want to get the minimum salary paid in Lagos.

How do we get this information from our table?

The following code shows how to use the MIN() function with the WHERE clause in SQL:

CREATE TABLE Person (
    ID int,
    name varchar(100),
    salary int,
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Person
VALUES (1,'Sharon Peller',65000,'Female','Kogi');
INSERT INTO Person
VALUES (2,'Paul Dons',150000,'Male','Lagos');
INSERT INTO Person
VALUES (3,'Ameera Abedayo',200000,'Female','Imo');
INSERT INTO Person
VALUES (4,'Maria Elijah',320000,'Female','Lagos');
INSERT INTO Person
VALUES (5,'David Hassan',250000,'Male','Abuja');
INSERT INTO Person
VALUES (6,'Niniola Disu',80000,'Female','Lagos');
INSERT INTO Person
VALUES (7,'Praise Dominion',340000,'Female','Lagos');
INSERT INTO Person
VALUES (7,'Divine Favour',280000,'Female','Abuja');
INSERT INTO Person
VALUES (7,'Praise Dominion',100000,'Female','Lagos');
INSERT INTO Person
VALUES (8,'Joe Smith',75000, 'Male', 'Lagos');
-- Query 
SELECT MIN(salary)
FROM Person
WHERE state = 'Lagos';

Explanation

  • Lines 1–7: We create a table called Person with columns id, name, salary, gender, and state.
  • Lines 10–29: We insert data into the Person table.
  • Lines 31–35: We got the minimum salary paid in Lagos using the MIN() function with the WHERE() clause.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)