SQL MAX() with group by
Max() function with Group by
In this page we are discussing, how the GROUP BY clause along with the SQL MAX() can be used to find the maximum value of a column over each group.
Example:
Sample table: agents
To get data of 'working_area' and maximum '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, MAX(commission) -- Selects the working_area column and the maximum value of the commission column for each working_area
FROM agents -- Specifies the 'agents' table as the source of data
GROUP BY working_area; -- Groups the result set by the working_area column
Explanation:
- SELECT working_area, MAX(commission): This part of the query selects two columns: 'working_area' and the maximum value of the 'commission' column for each 'working_area'. The MAX() function calculates the maximum value of the 'commission' column within each group of rows with the same 'working_area'.
- FROM agents: This specifies the source of the data for the query, which is the 'agents' 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 'agents' table.
- GROUP BY working_area: This clause groups the result set by the 'working_area' column. The GROUP BY clause is used to aggregate the rows based on the values in the 'working_area' column. This means that calculations performed in the SELECT statement (such as finding the maximum commission) will be applied separately for each unique value in the 'working_area' column.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
WORKING_AREA MAX(COMMISSION) ----------------------------------- --------------- San Jose .12 Torento .15 London .15 Hampshair .11 New York .12 Brisban .13 Bangalore .15 Chennai .14 Mumbai .11
SQL max() with group by and order by
Sample table: customer
To get data of 'cust_city', 'cust_country' and maximum '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 be arranged in alphabetical order,
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
ORDER BY cust_city; -- Orders the result set by cust_city
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'.
- ORDER BY cust_city: This clause orders the result set by the 'cust_city' column. The ORDER BY clause is used to sort the rows of the result set in ascending order of 'cust_city'.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
CUST_CITY CUST_COUNTRY MAX(OUTSTANDING_AMT) ----------------------------------- -------------------- -------------------- Bangalore India 12000 Brisban Australia 7000 Chennai India 11000 Hampshair UK 5000 London UK 11000 Mumbai India 12000 New York USA 6000 San Jose USA 3000 Torento Canada 11000
Pictorial Presentation:
SQL max() with group by on two columns
Sample table: customer
To get data of 'cust_city', 'cust_country' and maximum 'outstanding_amt' from the 'customer' table with the following condition -
1. the combination of 'cust_country' and 'cust_city' column 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
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'.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
CUST_CITY CUST_COUNTRY MAX(OUTSTANDING_AMT) ----------------------------------- -------------------- -------------------- Bangalore India 12000 Brisban Australia 7000 Chennai India 11000 Hampshair UK 5000 London UK 11000 Mumbai India 12000 New York USA 6000 San Jose USA 3000 Torento Canada 11000
Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition
Here is a slide presentation of all aggregate functions.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Max Function
Next: Max Having, Where, in
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics