SQL MIN() function with group by

MIN() function with group by

In this page we are going to discuss, how the GROUP BY clause along with the SQL MIN() can be used to find the minimum value of a column over each group.

Example:

Sample table: agents

To get data of 'working_area' and minimum value of 'commission' for the agents of each 'working_area' from the 'agents' table with the following condition -

1. the 'working_area' should come in a group,

the following SQL statement can be used :

SELECT working_area, MIN(commission) -- Selecting 'working_area' and the minimum 'commission'
FROM agents -- From the 'agents' table
GROUP BY working_area; -- Grouping the results by 'working_area'

Explanation:

• SELECT working_area, MIN(commission): This line selects two columns from the table: working_area and the minimum value of the commission column. The MIN() function is used to find the smallest value of the commission column within each group of working_area.


• FROM agents: This specifies the table from which data is being selected, which is the agents table.


• GROUP BY working_area: This line groups the results by the working_area column. When you use GROUP BY, you're essentially creating distinct groups based on the values in the specified column(s). In this case, it groups the agents by their working_area.

Output:

WORKING_AREA                        MIN(COMMISSION)
----------------------------------- ---------------
San Jose                                        .12
Torento                                         .15
London                                          .13
Hampshair                                       .11
New York                                        .12
Brisban                                         .13
Bangalore                                       .14
Chennai                                         .14
Mumbai                                          .11

SQL MIN() with group by and order by

Sample table: customer

To get data of 'cust_city', 'cust_country' and minimum or lowest value of 'outstanding_amt' from the 'customer' table with the following conditions -

1. the combination of 'cust_country' and 'cust_city' should make a group,

2. the group should come in alphabetical order,

the following SQL statement can be used :

SELECT cust_city, cust_country, MIN(outstanding_amt) -- Selecting 'cust_city', 'cust_country', and the minimum 'outstanding_amt'
FROM customer -- From the 'customer' table
GROUP BY cust_country, cust_city -- Grouping the results by 'cust_country' and 'cust_city'
ORDER BY cust_city; -- Ordering the results by 'cust_city'

Explanation:

• SELECT cust_city, cust_country, MIN(outstanding_amt): This line selects three columns from the table: cust_city, cust_country, and the minimum value of the outstanding_amt column. The MIN() function is used to find the smallest value of the outstanding_amt column within each group of cust_country and cust_city.


• FROM customer: This specifies the table from which data is being selected, which is the customer table.


• GROUP BY cust_country, cust_city: This line groups the results by the cust_country and cust_city columns. It essentially creates distinct groups based on the combination of cust_country and cust_city.


• ORDER BY cust_city: This line orders the grouped results by the cust_city column in ascending order. This ensures that the results are sorted alphabetically by city name.

Output:

CUST_CITY                           CUST_COUNTRY         MIN(OUTSTANDING_AMT)
----------------------------------- -------------------- --------------------
Bangalore                           India                                8000
Brisban                             Australia                            5000
Chennai                             India                                8000
Hampshair                           UK                                   5000
London                              UK                                   3000
Mumbai                              India                                9000
New York                            USA                                  3000
San Jose                            USA                                  3000
Torento                             Canada                               5000

SQL MIN() with group by on two columns

Sample table: customer

To get data of 'cust_city', 'cust_country' and minimum or lowest value of 'outstanding_amt' from the 'customer' table with the following condition -

1. the combination of 'cust_country' and 'cust_city' should make a group,

the following SQL statement can be used :

SELECT cust_city, cust_country, MIN(outstanding_amt) -- Selecting 'cust_city', 'cust_country', and the minimum 'outstanding_amt'
FROM customer -- From the 'customer' table
GROUP BY cust_country, cust_city; -- Grouping the results by 'cust_country' and 'cust_city'

Explanation:

• SELECT cust_city, cust_country, MIN(outstanding_amt): This line selects three columns from the table: cust_city, cust_country, and the minimum value of the outstanding_amt column. The MIN() function is used to find the smallest value of the outstanding_amt column within each group of cust_country and cust_city.


• FROM customer: This specifies the table from which data is being selected, which is the customer table.


• GROUP BY cust_country, cust_city: This line groups the results by the cust_country and cust_city columns. It essentially creates distinct groups based on the combination of cust_country and cust_city.

Output:

CUST_CITY                           CUST_COUNTRY         MIN(OUTSTANDING_AMT)
----------------------------------- -------------------- --------------------
Bangalore                           India                                8000
Brisban                             Australia                            5000
Chennai                             India                                8000
Hampshair                           UK                                   5000
London                              UK                                   3000
Mumbai                              India                                9000
New York                            USA                                  3000
San Jose                            USA                                  3000
Torento                             Canada                               5000

All Aggregate Functions

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Min function
Next: Min having, in, where