MySQL MAX() function with group by

Last update on April 27 2024 10:53:58 (UTC/GMT +8 hours)

MAX() function with group by

MySQL MAX() function with GROUP BY retrieves maximum value of an expression which has undergone a grouping operation (usually based upon one column or a list of comma-separated columns).

Example:

Sample table: book_mast

Code:


-- This SQL query retrieves the maximum book price for each category.
SELECT cate_id, -- Selects the category ID
       MAX(book_price) -- Calculates the maximum book price for each category
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY cate_id; -- Groups the results by category ID, so that the maximum book price is calculated for each category separately

Explanation:

This SQL query retrieves data from the book_mast table.

It calculates the maximum book price for each category.

The GROUP BY clause ensures that the results are grouped by category ID, allowing for the maximum book price to be calculated separately for each category.

Here's how the process works:

The query selects the category ID (cate_id).

It calculates the maximum book price for each category using the MAX(book_price) function.

The results are grouped by category ID using GROUP BY cate_id, ensuring that the maximum book price is calculated separately for each category.

Finally, the query returns the category ID and the maximum book price for each category.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

The above MySQL statement will extract all “cate_id”s and the maximum 'book_price' in each group of 'cate_id'. ‘GROUP BY ‘ clause have grouped “cate_id's”.

Output:

mysql> SELECT cate_id, MAX(book_price)
    -> FROM book_mast
    -> GROUP BY cate_id;
+---------+-----------------+
| cate_id | MAX(book_price) |
+---------+-----------------+
| CA001   |          145.00 | 
| CA002   |          250.00 | 
| CA003   |          200.00 | 
| CA004   |          100.00 | 
| CA005   |          180.00 | 
+---------+-----------------+
5 rows in set (0.02 sec)

Pictorial Presentation:

MySQL MAX() function with group by on two columns

Sample table: publisher

Code:


-- This SQL query retrieves the maximum number of branches for each combination of country and publisher city.
SELECT country, pub_city, MAX(no_of_branch) -- Selects the country, publisher city, and calculates the maximum number of branches for each combination of country and publisher city
FROM publisher -- Specifies the table from which to retrieve data (publisher table)
GROUP BY country, pub_city; -- Groups the results by country and publisher city, so that the maximum number of branches is calculated for each combination of country and city

Explanation:

This SQL query retrieves data from the publisher table.
It calculates the maximum number of branches for each combi

The GROUP BY clause ensures that the results are grouped by both country and publisher city, allowing for separate maximum number of branches to be calculated for each combination of country and city.

Here's how the process works:

The query selects the country (country), publisher city (pub_city), and calculates the maximum number of branches for each combination of country and publisher city using MAX(no_of_branch).

The results are grouped by both country and publisher city using GROUP BY country, pub_city, ensuring that the maximum number of branches is calculated separately for each combination of country and city.

Finally, the query returns the country, publisher city, and the maximum number of branches for each combination of country and publisher city.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

The above MySQL statement will extract those countries ('country') and publisher cities ('pub_city') which has the maximum number of branches ('no_of_branch') in each group of 'country' and 'pub_city'.

Output:

mysql> SELECT country,pub_city,MAX(no_of_branch)
    -> FROM publisher
    -> GROUP BY country,pub_city;
+-----------+-----------+-------------------+
| country   | pub_city  | MAX(no_of_branch) |
+-----------+-----------+-------------------+
| Australia | Adelaide  |                 6 | 
| India     | Mumbai    |                10 | 
| India     | New Delhi |                10 | 
| UK        | Cambridge |                 6 | 
| UK        | London    |                 8 | 
| USA       | Houstan   |                25 | 
| USA       | New York  |                15 | 
+-----------+-----------+-------------------+
7 rows in set (0.02 sec)

MySQL MAX with group by and order by

Code:


-- This SQL query retrieves the maximum number of branches for each combination of country and publisher city, ordered by country.
SELECT country, pub_city, MAX(no_of_branch) -- Selects the country, publisher city, and calculates the maximum number of branches for each combination of country and publisher city
FROM publisher -- Specifies the table from which to retrieve data (publisher table)
GROUP BY country, pub_city -- Groups the results by country and publisher city, so that the maximum number of branches is calculated for each combination of country and city
ORDER BY country; -- Orders the results by country in ascending order

Explanation:

This SQL query retrieves data from the publisher table.

It calculates the maximum number of branches for each combination of country and publisher city.

The GROUP BY clause ensures that the results are grouped by both country and publisher city, allowing for separate maximum number of branches to be calculated for each combination of country and city.

The ORDER BY clause specifies that the results should be ordered by country in ascending order.

Here's how the process works:

The query selects the country (country), publisher city (pub_city), and calculates the maximum number of branches for each combination of country and publisher city using MAX(no_of_branch).

The results are grouped by both country and publisher city using GROUP BY country, pub_city, ensuring that the maximum number of branches is calculated separately for each combination of country and city.

Finally, the query orders the results by country in ascending order using ORDER BY country.

The query returns the country, publisher city, and the maximum number of branches for each combination of country and publisher city, ordered by country.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

The above MySQL statement will extract those countries ('country') and publisher cities ('pub_city') which have the maximum number of branches ('no_of_branch') for each group of 'country' and 'pub_city'. ‘GROUP BY ‘ clause have grouped 'country' and 'pub_city' . The 'country' column have sorted in ascending order by the usage of ORDER BY clause.

Output:

mysql> SELECT country,pub_city,MAX(no_of_branch)
    -> FROM publisher
    -> GROUP BY country,pub_city
    -> ORDER BY country;
+-----------+-----------+-------------------+
| country   | pub_city  | MAX(no_of_branch) |
+-----------+-----------+-------------------+
| Australia | Adelaide  |                 6 | 
| India     | Mumbai    |                10 | 
| India     | New Delhi |                10 | 
| UK        | Cambridge |                 6 | 
| UK        | London    |                 8 | 
| USA       | Houstan   |                25 | 
| USA       | New York  |                15 | 
+-----------+-----------+-------------------+
7 rows in set (0.00 sec)

MySQL MAX() function with distinct

MAX() function with distinct

MySQL MAX() function retrieves the maximum value of an expression if the function is accompanied by a DISTINCT clause.

Example:

Sample table: book_mast

Code:


-- This SQL query retrieves the maximum number of pages for each category, considering distinct values.
SELECT cate_id, MAX(DISTINCT no_page) -- Selects the category ID and calculates the maximum number of pages for each category, considering distinct values
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY cate_id; -- Groups the results by category ID, so that the maximum number of pages is calculated for each category separately

Explanation:

This SQL query retrieves data from the book_mast table.

It calculates the maximum number of pages for each category, considering distinct values.

The GROUP BY clause ensures that the results are grouped by category ID, allowing for the maximum number of pages to be calculated separately for each category.

Here's how the process works:

The query selects the category ID (cate_id).

It calculates the maximum number of pages for each category, considering distinct values of no_page, using the MAX(DISTINCT no_page) function.

The results are grouped by category ID using GROUP BY cate_id, ensuring that the maximum number of pages is calculated separately for each category.

Finally, the query returns the category ID and the maximum number of pages for each category, considering distinct values.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

The above MySQL statement will extract category ('cat_id') wise maximum number of pages ('no_page') from the 'book_mast' table.

Output:

mysql> SELECT cate_id,MAX(DISTINCT no_page)
    -> FROM book_mast
    -> GROUP BY cate_id;
+---------+-----------------------+
| cate_id | MAX(DISTINCT no_page) |
+---------+-----------------------+
| CA001   |                   345 | 
| CA002   |                   600 | 
| CA003   |                   510 | 
| CA004   |                   350 | 
| CA005   |                   350 | 
+---------+-----------------------+
5 rows in set (0.00 sec)

Previous: Max()
Next: Max() with having