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.
- 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
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
WHERE clauses, but before the
In the sections below, we’ll use data from the Orders table to illustrate examples.
|1||10||Letha Wahl||Baldwin Park||1125.00|
GROUP BY syntax in MySQL
GROUP BY is often used with the aggregation functions (
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;
expression1, expression2, … expression_n– specifies expressions that are included in the aggregation function, but must be added to
tables– lists the tables you want to retrieve records from. At least one table must be specified in the
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
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
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
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
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
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
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
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
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
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
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:
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!