Here we will learn what is SQLite Min() function and how to use SQLite Min() function with WHERE, Group By, Having to get the minimum value from a column with examples.
In SQLite MIN() Function is an aggregate function which is used to get the minimum value of specified expression or column.
Generally, in SQLite MIN() function will work with non-NULL numeric values to return the minimum value of the column in a table. In case if the table column contains only NULL values means MIN() function will return NULL.
Following is the syntax of the SQLite min() function to get the minimum value of a defined expression.
SELECT MIN(Expression) FROM tablename
[WHERE condition]
[GROUP BY Expression];
In above SQLite MIN() function syntax, we defined multiple parameters those are
Expression – Its column or expression which we used to get the minimum value of defined column or expression.
Tablename – Its name of the table which we wish to retrieve records from.
[WHERE Condition] – If we have any specific conditions to get the minimum value of column then we need to specify WHERE based on our requirements. It’s optional.
[Group By Expression] – If we want to get minimum value based on particular Expression then we need to use Group By. It’s an optional.
Now we will see how to use MIN() function in SQLite to get the minimum value of an expression with examples for that we need to create two tables called dept_master and emp_master using following query.
To create and insert some data in the dept_master table execute the following query.
CREATE TABLE dept_master
(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
dept_name TEXT);
INSERT INTO dept_master(dept_name)
VALUES('Admin'),
('Sales'),
('Quality Control'),
('Marketing');
Once we create and insert data execute the following query to see the data of dept_master table.
sqlite> SELECT * FROM dep_master;
dept_id dept_name
---------- ----------
1 Admin
2 Sales
3 Quality Control
4 Marketing
Same way execute the following queries to create and insert some data in emp_master table.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (1,'Honey','Patel', 10100,1),
(2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,2),
(5,'Shweta','Rana',12000,3),
(6,'sonal','Menpara', 13000,1),
(7,'Yamini','Patel', 10000,2),
(8,'Khyati','Shah', 50000,3),
(9,'Shwets','Jariwala',19400,2);
Now run the following query to check the records of emp_master table.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
9 Shwets Jariwala 19400 2
Now write the query like as shown following to get the minimum salary of employees from the emp_master table.
sqlite> SELECT MIN(salary) FROM emp_master;
MIN(salary)
-----------
9500
Suppose in SQLite if we want to get the minimum value of an expression based on the group like calculating the minimum salary of each department then we need to use SQLite MIN() function with Group By clause to return minimum value based on our requirements.
Following is the SQLite query to use MIN() function with Group By clause to get the minimum salary of each department.
sqlite> SELECT d.dept_name,min(e.salary),e.first_name
FROM emp_master e, dept_master d
WHERE e.dept_id = d.dept_id
GROUP BY e.dept_id;
dept_name min(e.salary) first_name
---------- ------------- ----------
Admin 10100 Honey
Sales 9500 Jagruti
Quality Control 12000 Shweta
If you observe the above query we got the minimum salary of each department by using Group By clause with SQLite MIN() function.
Suppose if we want to get the minimum value of a particular group alone then by using a Having clause with SQLite MIN() function, we can easily get the required result.
Following is the SQLite query which will return the minimum salary of each department whose department name starts with “q”.
sqlite> SELECT d.dept_name,MIN(e.salary),e.first_name
FROM emp_master e, dept_master d
WHERE e.dept_id = d.dept_id
GROUP BY e.dept_id
HAVING d.dept_name LIKE 'q%';
dept_name min(e.salary) first_name
--------------- ------------- ----------
Quality Control 12000 Shweta
The above example returns the minimum salary of each department whose department name starts with “q”.
This is how we can use SQLite MIN() function to get the minimum value of column in a table based on our requirements.