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 dataINSERT INTO PersonVALUES (1,'Sharon Peller',65000,'Female','Kogi');INSERT INTO PersonVALUES (2,'Paul Dons',150000,'Male','Lagos');INSERT INTO PersonVALUES (3,'Ameera Abedayo',200000,'Female','Imo');INSERT INTO PersonVALUES (4,'Maria Elijah',320000,'Female','Lagos');INSERT INTO PersonVALUES (5,'David Hassan',250000,'Male','Abuja');INSERT INTO PersonVALUES (6,'Niniola Disu',80000,'Female','Lagos');INSERT INTO PersonVALUES (7,'Praise Dominion',340000,'Female','Lagos');INSERT INTO PersonVALUES (7,'Divine Favour',280000,'Female','Abuja');INSERT INTO PersonVALUES (7,'Praise Dominion',100000,'Female','Lagos');INSERT INTO PersonVALUES (8,'Joe Smith',75000, 'Male', 'Lagos');-- QuerySELECT MIN(salary)FROM PersonWHERE state = 'Lagos';
Explanation
- Lines 1–7: We create a table called
Personwith columnsid,name,salary,gender, andstate. - Lines 10–29: We insert data into the
Persontable. - Lines 31–35: We got the minimum salary paid in Lagos using the
MIN()function with theWHERE()clause.
TRENDING TOPICS
