SQL MIN() with HAVING

MIN() with HAVING

In this page, we are going to discuss the usage of SQL HAVING clause with SQL MIN() function to find the lowest value of a column over each group against some condition.

Example:

Sample table: customer

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

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

2. 'outstanding_amt' must be more than 6000,

the following SQL statement can be used :

SELECT cust_city, cust_country, -- 1. Selecting 'cust_city', 'cust_country', 
                                 --    and the minimum 'outstanding_amt'
MIN(outstanding_amt) -- 2. Finding the minimum 'outstanding_amt'
FROM customer -- 3. From the 'customer' table
GROUP BY cust_country, cust_city -- 4. Grouping the results by 'cust_country' and 'cust_city'
HAVING MIN(outstanding_amt) > 6000; -- 5. Filtering the groups based on a condition

Explanation:

• The query starts by selecting three columns: cust_city, cust_country, and the minimum outstanding_amt from the customer table.


• It groups the results by cust_country and cust_city. This means that it groups customers based on their city and country.


• Then, it applies a filter using the HAVING clause. This clause is similar to the WHERE clause but is specifically used with aggregate functions (like MIN() in this case) to filter grouped results. It ensures that only groups with a minimum outstanding_amt greater than 6000 are included in the final result.


• The result of this query will be a list of cities and countries where the minimum outstanding amount for any customer in that city and country combination is greater than 6000.

Output:

CUST_CITY                           CUST_COUNTRY         MIN(OUTSTANDING_AMT)
----------------------------------- -------------------- --------------------
Bangalore                           India                                8000
Chennai                             India                                8000
Mumbai                              India                                9000

SQL MIN() with HAVING, IN using group by

Sample table: customer

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

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

2. the 'opening_amt' should be 3000 or 8000 or 10000,

the following SQL statement can be used :

SELECT opening_amt, MIN(outstanding_amt) -- 1. Selecting 'opening_amt' and the minimum 'outstanding_amt'
FROM customer -- 2. From the 'customer' table
GROUP BY opening_amt -- 3. Grouping the results by 'opening_amt'
HAVING opening_amt IN (3000, 8000, 10000); -- 4. Filtering the groups based on a condition

Explanation:

• The query starts by selecting two columns: opening_amt and the minimum outstanding_amt from the customer table.


• It groups the results by opening_amt. This means that it groups customers based on the value of their opening amount.


• Then, it applies a filter using the HAVING clause. This clause is similar to the WHERE clause but is specifically used with aggregate functions (like MIN() in this case) to filter grouped results. It ensures that only groups with an opening_amt of 3000, 8000, or 10000 are included in the final result.


• The result of this query will be a list of opening amounts along with the minimum outstanding amount for customers whose opening amount matches any of the specified values (3000, 8000, or 10000).

Output:

OPENING_AMT MIN(OUTSTANDING_AMT)
----------- --------------------
      10000                11000
       3000                 6000
       8000                 8000

SQL MIN() in where

Sample table: customer

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

1. 'cust_city' and 'cust_country' should be formatted in a group,

2. 'grade' must be 2,

the following SQL statement can be used:

SELECT cust_city, cust_country, -- Selecting 'cust_city', 'cust_country', 
                                 --    and the minimum 'outstanding_amt'
MIN(outstanding_amt) -- Finding the minimum 'outstanding_amt'
FROM customer -- From the 'customer' table
WHERE grade = 2 -- Filtering the rows where the 'grade' column equals 2
GROUP BY cust_city, cust_country; -- Grouping the results by 'cust_city' and 'cust_country'

Explanation:

• The query starts by selecting three columns: cust_city, cust_country, and the minimum outstanding_amt from the customer table.


• It includes a WHERE clause which filters the rows from the customer table where the grade column equals 2. This means it will only consider customers with a grade of 2.


• The results are then grouped by cust_city and cust_country. This means that it groups customers based on their city and country.


• The query calculates the minimum outstanding_amt within each group defined by cust_city and cust_country.


• The result of this query will be a list of cities and countries where the minimum outstanding amount for any customer in that city and country combination, who has a grade of 2, is displayed.

Output:

CUST_CITY                           CUST_COUNTRY         MIN(OUTSTANDING_AMT)
----------------------------------- -------------------- --------------------
Bangalore                           India                                8000
Brisban                             Australia                            5000
London                              UK                                   4000
Mumbai                              India                                9000
New York                            USA                                  6000
Torento                             Canada                               8000

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 group by, order by
Next: Min count