SQLite MIN() Function

  Total Views : 187
  Comments : 0
  Keywords : SQLite min function with example, SQLite get minimum value from defined expression using min() function, SQLite min() function to return minimum value with example

Here we will learn what is SQLite Min() function and how to use SQLite Min() function with WHERE, Group By, Having to get minimum value from a column with examples.

SQLite MIN() Function

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 column in a table. In case if table column contains only NULL values means MIN() function will return NULL.

Syntax of SQLite MIN() Function

Following is the syntax of SQLite min() function to get the minimum value of a defined expression.

 

SELECT MIN(ExpressionFROM 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 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.

Example of SQLite MIN() Function

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 dept_master table execute 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 following query to see the data of dept_master table.

 

sqliteSELECT FROM dep_master;

 

dept_id     dept_name

----------  ----------

1           Admin

2           Sales

3           Quality Control

4           Marketing

Same way execute 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.

 

sqliteSELECT 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 emp_master table.

 

sqliteSELECT MIN(salaryFROM emp_master;

 

MIN(salary)

-----------

9500

SQLite MIN() Function with Group By Clause

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.

 

sqliteSELECT 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 above query we got the minimum salary of each department by using Group By clause with SQLite MIN() function.

SQLite MIN() Function with Having Clause

Suppose if we want to get the minimum value of particular group alone then by using Having clause with SQLite MIN() function we can easily get the required result.

 

Following is the SQLite query which will return minimum salary of each department whose department name starts with “q”.

 

sqliteSELECT 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 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. 

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User