w3resource

PostgreSQL MIN function

MIN function

The MIN function determines the smallest of all selected values of a column.

Syntax:

MIN (* | [DISTINCT] ALL | column_name)

Parameters

Name Description
column_name Name of the column
* The asterisk(*) indicates all the rows.
DISTINCT This clause is optional. It indicates uniqueness.
ALL This clause is optional. It is default clause.

Contents:

PostgreSQL MIN function example

The sample table

postgresql sample table employee example1

If we want to find the minimum salary from all employees in the employee table, the following SQL can be used.

Code:

SELECT MIN(salary) 
FROM employee;

Output:

postgresql min function example1

Pictorial Presentation of PostgreSQL MIN()

postgresql MIN function

PostgreSQL MIN as a level

If we want to get the minimum salary for all employees and show the result against 'Minimum Salary' head in the employee table, the following SQL can be used.

SQL

Code:

SELECT MIN(salary) "Minimum Salary" 
FROM employee;

OR

Code:

SELECT MIN(DISTINCT salary) "Minimum Salary" 
FROM employee;

OR

Code:

SELECT MIN(ALL salary) "Minimum Salary" 
FROM employee;

Output:

postgresql min function as a level name

PostgreSQL MIN function two columns

If we want to get the minimum salary and deduction from employee table, the following SQL can be used.

SQL

Code:

SELECT MIN(salary) "Minimum Salary" ,
MIN(deduction) "Minimum Deduction"
FROM employee;

Output:

postgresql min function two columns

PostgreSQL MIN with MAX, COUNT, and SUM

If we want to get the minimum salary and deduction from employee table, the following SQL can be used.

SQL

Code:

SELECT MIN(salary) "Minimum Salary" ,
MAX(salary) "Maximum Salary",
COUNT(*) "Total Employee",
SUM(salary) "Total Salary"
FROM employee;

Output:

postgresql min function with max,count and sum

PostgreSQL MIN WHERE clause

If we want to get the minimum salary and deduction from employee table who belongs to the designation 'SALESMAN', the following SQL can be used.

SQL

Code:

SELECT MIN(salary) "Minimum Salary" ,
MIN(deduction) "Minimum Deduction"
FROM employee
WHERE designame='SALESMAN';

Output:

postgresql min function where clause

PostgreSQL MIN with GROUP BY

Sample table: employees


If we want to get the minimum salary for each designation available in employees table, the following SQL can be used.

SQL

Code:

SELECT job_id, MIN(salary) AS "Minimum Salary" 
FROM employees 
GROUP BY job_id;

Sample Output:

   job_id   | Minimum Salary
------------+---------------
 AC_ACCOUNT |        8300.00
 ST_MAN     |        5800.00
 IT_PROG    |        4200.00
 SA_MAN     |       10500.00
 AD_PRES    |       24000.00
 AC_MGR     |       12000.00
 FI_MGR     |       12000.00
 AD_ASST    |        4400.00
 MK_MAN     |       13000.00
 PU_CLERK   |        2500.00
 HR_REP     |        6500.00
 PR_REP     |       10000.00
 FI_ACCOUNT |        6900.00
 SH_CLERK   |        2500.00
 AD_VP      |       17000.00
 SA_REP     |        6100.00
 ST_CLERK   |        2100.00
 MK_REP     |        6000.00
 PU_MAN     |       11000.00
(19 rows)

Pictorial Presentation of PostgreSQL MIN with GROUP BY

postgresql MIN function with GROUP BY

PostgreSQL MIN with HAVING CLAUSE

Sample table: employees


If we want to get those designations, whose minimum salary is 6500 and above within the salary range below 12000, the following SQL can be used.

SQL

Code:

SELECT job_id,MIN(salary) AS "Minimum Salary" 
FROM employees 
WHERE salary<12000 
GROUP BY job_id 
HAVING MIN(salary)>=6500;

Sample Output:

   job_id   | Minimum Salary
------------+----------------
 AC_ACCOUNT |        8300.00
 SA_MAN     |       10500.00
 HR_REP     |        6500.00
 PR_REP     |       10000.00
 FI_ACCOUNT |        6900.00
 PU_MAN     |       11000.00
(6 rows)

Pictorial Presentation of PostgreSQL MIN with HAVING

postgresql MIN function with HAVING

PostgreSQL MIN with GROUP BY and ORDER BY

Sample table: employees


The following query will return the designation which minimum salary is 6500 and above within the salary range below 12000 and the minimum salary for each designation comes in the list in descending order.

SQL

Code:

SELECT job_id,MIN(salary) AS "Minimum Salary" 
FROM employees 
WHERE salary<12000
GROUP BY job_id 
HAVING MIN(salary)>=6500
ORDER BY MIN(salary) DESC;

Sample Output:

   job_id   | Minimum Salary
------------+----------------
 PU_MAN     |       11000.00
 SA_MAN     |       10500.00
 PR_REP     |       10000.00
 AC_ACCOUNT |        8300.00
 FI_ACCOUNT |        6900.00
 HR_REP     |        6500.00
(6 rows)

Previous: MAX
Next: AVG



Share this Tutorial / Exercise on : Facebook and Twitter