SQL AGGREGATE FUNCTIONS – MIN
SQL has 5 different aggregate functions (MIN, MAX, COUNT, SUM & AVG). Here we will be looking at the MIN function. The MIN function returns the minimum value of a column in the table.
SYNTAX –
SELECT MIN(column1) FROM table_name WHERE condition;
Usually, the aggregate functions are used in conjunction with the GROUP BY/ HAVING clause, which gives even more meaningful results. The syntax for the query using MIN function using GROUP BY / HAVING clause is mentioned below –
SELECT column1, MIN(column2) FROM table_name WHERE condition1 GROUP BY column1 HAVING condition2;
At first, these might look little complicated, but once we go over few examples, it will make more sense.
Illustration – Consider the below employee table in the database which stores the information pertaining to employees in a company –
ID | employee_name | age | salary | Department | manager_name |
---|---|---|---|---|---|
1 | Jimmy | 32 | 80000 | Sales | Vijay |
2 | James | 35 | 85000 | Sales | Vijay |
3 | Helen | 33 | 82000 | HR | Krish |
4 | Mary | 26 | 78000 | Quality | Warner |
5 | William | 33 | 90000 | HR | Krish |
6 | Vijay | 38 | 105000 | Sales | Roger |
7 | Krish | 38 | 105000 | HR | Roger |
Query 1 – What is the minimum salary earned by any employee in ABC company?
SELECT MIN(salary) FROM employee;
MIN(salary)
—————
78000
Query 2 – What is the minimum salary earned by any employee in ABC company whose age is greater than 30 years?
SELECT MIN(salary) FROM employee WHERE age>30;
MIN(salary)
—————
80000
Query 3 – What is the minimum salary earned by employees in ABC company by each department.
SELECT Department, MIN(salary) FROM employee GROUP BY Department;
Department | MIN(Salary) |
---|---|
Sales | 80000 |
HR | 82000 |
Quality | 78000 |
Query 4 – What is the minimum salary earned by employees in ABC company by each department that gets at least 80000 as salary.
SELECT Department, MIN(salary) FROM employee GROUP BY Department HAVING MIN(salary) >= 80000;
Department | MIN(Salary) |
---|---|
Sales | 80000 |
Quality | 78000 |