SQL MAX() with HAVING

Last update on April 05 2024 08:28:57 (UTC/GMT +8 hours)

MAX() function with Having

In this article we have discussed how SQL HAVING CLAUSE can be used along with the SQL MAX() to find the maximum value of a column over each group. The SQL HAVING CLAUSE is reserved for aggregate function.

The usage of WHERE clause along with SQL MAX() have also described in this page.

The SQL IN OPERATOR which checks a value within a set of values and retrieve the rows from the table can also be used with MAX function.

Example :

Sample table :customer

To get data of 'cust_city', 'cust_country' and maximum 'outstanding_amt' from the customer table with following conditions -

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, MAX(outstanding_amt)  -- Selects the cust_city, cust_country, and the maximum outstanding_amt for each combination of cust_country and cust_city
FROM customer  -- Specifies the 'customer' table as the source of data
GROUP BY cust_country, cust_city  -- Groups the result set by cust_country and cust_city
HAVING MAX(outstanding_amt) > 10000;  -- Filters the grouped results to include only those where the maximum outstanding_amt is greater than 10000

Explanation:

SELECT cust_city, cust_country, MAX(outstanding_amt): This part of the query selects three columns: 'cust_city', 'cust_country', and the maximum value of the 'outstanding_amt' column for each combination of 'cust_country' and 'cust_city'. The MAX() function calculates the maximum value of the 'outstanding_amt' column within each group of rows with the same combination of 'cust_country' and 'cust_city'.

FROM customer: This specifies the source of the data for the query, which is the 'customer' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'customer' table.

GROUP BY cust_country, cust_city: This clause groups the result set by the 'cust_country' and 'cust_city' columns. The GROUP BY clause is used to aggregate the rows based on the values in these columns. This means that calculations performed in the SELECT statement (such as finding the maximum outstanding_amt) will be applied separately for each unique combination of 'cust_country' and 'cust_city'.

HAVING MAX(outstanding_amt) > 10000: This clause filters the grouped results to include only those groups where the maximum outstanding_amt is greater than 10000. The HAVING clause is used in combination with aggregate functions like MAX() to filter the grouped results based on aggregate values.

Relational Algebra Expression:

Relational Algebra Tree:

Output :

CUST_CITY                           CUST_COUNTRY         MAX(OUTSTANDING_AMT)
----------------------------------- -------------------- --------------------
Bangalore                           India                               12000
Chennai                             India                               11000
London                              UK                                  11000
Mumbai                              India                               12000
Torento                             Canada                              11000

Pictorial Presentation :

SQL MAX() in where

Sample table : customer

To get data of 'cust_country' and maximum 'outstanding_amt' from the 'customer' table with following conditions -

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

2. 'grade' must be 2,

the following SQL statement can be used :


SELECT cust_country, MAX(outstanding_amt)  -- Selects the cust_country and the maximum outstanding_amt for each cust_country
FROM customer  -- Specifies the 'customer' table as the source of data
WHERE grade = 2  -- Filters the rows to include only those where the grade column is equal to 2
GROUP BY cust_country;  -- Groups the result set by cust_country

Explanation:

SELECT cust_country, MAX(outstanding_amt): This part of the query selects two columns: 'cust_country' and the maximum value of the 'outstanding_amt' column for each 'cust_country'. The MAX() function calculates the maximum value of the 'outstanding_amt' column within each group of rows with the same 'cust_country'.

FROM customer: This specifies the source of the data for the query, which is the 'customer' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'customer' table.

WHERE grade = 2: This clause filters the rows from the 'customer' table to include only those where the 'grade' column is equal to 2. It restricts the data to only include customers with a grade of 2.

GROUP BY cust_country: This clause groups the result set by the 'cust_country' column. The GROUP BY clause is used to aggregate the rows based on the values in the 'cust_country' column. This means that calculations performed in the SELECT statement (such as finding the maximum outstanding_amt) will be applied separately for each unique value in the 'cust_country' column.

Relational Algebra Expression:

Relational Algebra Tree:

Output :

CUST_COUNTRY         MAX(OUTSTANDING_AMT)
-------------------- --------------------
USA                                  6000
India                               12000
Australia                            5000
Canada                               8000
UK                                   6000

Pictorial Presentation :

SQL MAX() with IN operator

Sample table : customer

To get data of 'opening_amt' and maximum 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, MAX(outstanding_amt)  -- Selects the opening_amt and the maximum outstanding_amt for each opening_amt
FROM customer  -- Specifies the 'customer' table as the source of data
GROUP BY opening_amt  -- Groups the result set by opening_amt
HAVING opening_amt IN (3000, 8000, 10000);  -- Filters the grouped results to include only those with opening_amt values of 3000, 8000, or 10000

Explanation:

SELECT opening_amt, MAX(outstanding_amt): This part of the query selects two columns: 'opening_amt' and the maximum value of the 'outstanding_amt' column for each 'opening_amt'. The MAX() function calculates the maximum value of the 'outstanding_amt' column within each group of rows with the same 'opening_amt'.

FROM customer: This specifies the source of the data for the query, which is the 'customer' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'customer' table.

GROUP BY opening_amt: This clause groups the result set by the 'opening_amt' column. The GROUP BY clause is used to aggregate the rows based on the values in the 'opening_amt' column. This means that calculations performed in the SELECT statement (such as finding the maximum outstanding_amt) will be applied separately for each unique value in the 'opening_amt' column.

HAVING opening_amt IN (3000, 8000, 10000): This clause filters the grouped results to include only those groups where the 'opening_amt' values are either 3000, 8000, or 10000. The HAVING clause is used in combination with the IN operator to filter groups based on specific values.

Output :

OPENING_AMT MAX(OUTSTANDING_AMT)
----------- --------------------
      10000                11000
       3000                 6000
       8000                12000

Pictorial Presentation :