GROUP BY Statement in MySQL With Twelve Examples

In this article, we’re going to share the MySQL tutorial how to use the MySQL GROUP BY function as well as this function usage together with and without aggregate functions.

Contents

• What is the meaning of the GROUP BY clause in MySQL?
• GROUP BY syntax in MySQL
• Simple GROUP BY example
• GROUP BY and the HAVING clause
• GROUP BY with JOIN
• GROUP BY and the WITH ROLLUP modifier
• GROUP BY and ORDER BY
• GROUP BY with multiple columns
• GROUP BY with the SUM function
• GROUP BY with the MAX function
• GROUP BY with the MIN function
• GROUP BY and the DISTINCT clause
• How to use LIMIT with GROUP BY in MySQL
• MySQL GROUP BY optimization using dbForge tools
• Conclusion

What is the meaning of the GROUP BY clause in MySQL?

GROUP BY is one of the most useful MySQL clauses. It groups rows with the same values into summary rows. The clause returns one row for each group. In a query, MySQL GROUP BY is located after the FROM and WHERE clauses, but before the HAVING, SELECT, DISTINCT, ORDER BY, LIMIT clauses.

In the sections below, we’ll use data from the Orders table to illustrate examples.

id	product_quantity	client_name	city	total_cost
1	10	Letha Wahl	Baldwin Park	1125.00
2	54	Stephan Boudreau	Marfa	550.00
3	3	Johnston Conyers	Geismar	125.00
4	32	Chantel Skaggs	Waxahachie	320.00
5	14	Mollie Boucher	Dighton	25.00
6	32	Mia Chesterfield	Marfa	25.00
7	54	Thomas Johnson	Dighton	125.00

GROUP BY syntax in MySQL

MySQL GROUP BY is often used with the aggregation functions (COUNT(), MAX(), MIN(), SUM(), AVG()) in order to group results by one or more columns. The basic syntax for the clause is as follows:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters:

• expression1, expression2, … expression_n – specifies expressions that are included in the aggregation function, but must be added to GROUP BY
• tables – lists the tables you want to retrieve records from. At least one table must be specified in the FROM clause
• aggregate_function – specifies an aggregation function
• WHERE conditions – optional parameter for defining conditions for records to be selected

Simple GROUP BY example

Here is an example of MySQL GROUP BY with the COUNT aggregation function:

SELECT city, COUNT(product_quantity)
FROM orders     
GROUP BY city;

Using the above syntax, we’ll know the number of products for each city.

GROUP BY and the HAVING clause

You can add HAVING to the GROUP BY syntax to find rows with particular conditions. The HAVING clause is used instead of WHERE with aggregate functions. HAVING is always placed after GROUP BY.

The statement below returns ids and the total cost that is less than 500:

SELECT id, total_cost
FROM orders
GROUP BY id
HAVING SUM(total_cost) < 500; 

GROUP BY with JOIN

MySQL GROUP BY can be combined with JOIN in order to group rows with the same values from one or several tables, based on a related column between them. There are three types of JOINS in MySQL, but in this section, we’ll review INNER JOIN.

So, let’s imagine that you want to get rows with the same values from the Employees and Orders tables on the basis of matching values between them. Then you can run the query below:

SELECT orders.city, COUNT(*)
FROM employees
             INNER JOIN orders
                        ON employees.city = orders.city
GROUP BY orders.city

GROUP BY and the WITH ROLLUP modifier

Using the WITH ROLLUP modifier with GROUP BY allows including to the summary output extra rows that indicate higher-level (super-aggregate) summary processes. For example, if you want to retrieve the total number of products and group them by id, you can run the following query:

SELECT id, SUM(product_quantity)
FROM orders
GROUP BY id WITH ROLLUP;

In the output, you will see the total number in the last row.

GROUP BY and ORDER BY

MySQL GROUP BY and ORDER BY can be used together to group rows with the same values and to sort the result in ascending or descending order. ORDER BY is always placed after GROUP BY. If you do not specify the order in the query, the ascending order will be applied.

The query below groups and sorts clients by cities in the descending order:

SELECT city, COUNT(*) 
FROM orders 
GROUP BY city 
ORDER BY COUNT(*) DESC;

GROUP BY with multiple columns

You can use MySQL GROUP BY to group several columns from a table at the same time. In this example, we’ll show how to group by city and product_quantity fields and count the number of grouped rows:

SELECT city, product_quantity, COUNT(id) 
FROM orders 
GROUP BY product_quantity, city;

GROUP BY with the SUM function

MySQL SUM is an aggregation function and is often used with GROUP BY to calculate the sum of the values. The statement below returns the total cost of products according to their ids:

SELECT id, SUM(total_cost) 
FROM orders
GROUP BY id;

GROUP BY with the MAX function

MySQL MAX with the GROUP BY statement retrieves the maximum value of a specified expression. Using the query below, you can get the total cost of products for each city:

SELECT city, MAX(total_cost)
FROM orders 
GROUP BY city;

GROUP BY with the MIN function

MySQL MIN function is used in the combination with GROUP BY to search for the minimum value of a specified expression. For example, using the statement below, you can obtain the lowest number of products for each city:

SELECT city, MIN(product_quantity)
FROM orders
GROUP BY city;

GROUP BY and the DISTINCT clause

The DISTINCT clause is used instead of GROUP BY if there is no aggregate function in the SELECT statement. For example, if you run both queries, you will get the same result:

SELECT product_quantity, client_name
FROM orders     
GROUP BY product_quantity, client_name;

SELECT DISTINCT product_quantity, client_name
FROM orders;    

The difference between both clauses is that GROUP BY sorts rows with the same values, whereas the DISTINCT clause doesn’t do that.

How to use LIMIT with GROUP BY in MySQL

If you need to set a specified number of rows that will be displayed in the output, you can use the LIMIT clause. In the query below, we want to calculate the average cost and get the first five records:

SELECT city, AVG(total_cost) 
FROM orders 
WHERE city IS NOT NULL 
GROUP BY city 
LIMIT 5;

MySQL GROUP BY optimization using dbForge tools

Database developers have to process many tasks every day. Usually, the tasks are estimated and sometimes it’s a challenge to complete a task within a certain period. That’s where GUI tools become the most desired and helpful. The right GUI tool can save time and turn your work into a pleasure.

If you want to make less effort, but get the maximum result, you can try the MySQL GUI tool from the dbForge product line. In comparison with other similar tools, this one has advanced features for developing, administering, and managing MySQL and MariaDB databases.

You don’t need to have specific SQL code knowledge to start working with this GUI tool because it includes the MySQL query generator. With this generator, you can select rows to be grouped, specify the clause, and you will get the ready query:

This MySQL GUI tool can greatly save your efforts by formatting your queries. Every time you type, the tool suggests you MySQL code snippets that you can use to complete the query.

With the dbForge GUI tool for MySQL, you can be totally focused on writing the statement. While the MySQL syntax checker finds errors in the query and highlights them for you to be able to correct them immediately. Thus, you don’t need to waste your time on searching for a typo, it will be underlined with a red squiggly line.

And additionally, you will get the whole description of the error to understand what is wrong:

Conclusion

In the article, we have reviewed the usage of MySQL GROUP BY with examples. As you can see, the statement can be combined with various clauses. For database administrators, it’s very important to know those clauses and be able to use them correctly. That’s where a mighty MySQL GUI tool that not only facilitates database administrators’ work but also becomes a reliable assistant in database development can be of great help. If you need such a tool, download dbForge GUI tool for MySQL for a free 30-day trial and try its advanced functionality in action!