How to Use the GROUP BY Clause in MySQL
In this tutorial, you'll learn how to use the MySQL GROUP BY clause in the SELECT query to group a set of records into summary records.
GROUP BY is used to group a set of records by one or more columns, and we often use it with aggregate functions such as COUNT(), SUM(), AGV(), MAX(), and MIN().
For example, let's say you have three salespersons (Mr. A, Mr. B, and Mr. C) in your company, and you want to know the number and total amount of sales by each salesperson. In this case, you can use the COUNT() function to count the number of the sales and the SUM() function to sum the amount with the GROUP BY clause.
MySQL GROUP BY Syntax
The syntax of the MySQL GROUP BY clause in the SELECT statement is as follows:
SELECT column_name1, column_name2, column_name3,... ,
aggregate_function(column_nameN)FROM table_name
WHERE where_conditions
GROUP BY column_name1, column_name2, column_name3,... ;
GROUP BY Clause Examples
Suppose you have the orders table with the demo data below:
| order_date | order_number | customer | amount | salesperson |
| 2019-10-07 | #ORD_100001 | Lisa Ramsey | 1980.00 | Erick Little |
| 2019-10-07 | #ORD_100002 | Lisa Ramsey | 1350.50 | Ronald Houston |
| 2019-10-07 | #ORD_100003 | Iris Wilkerson | 1230.40 | Ronald Houston |
| 2019-10-08 | #ORD_100004 | Clifton Christensen | 1300.00 | Erick Little |
| 2019-10-08 | #ORD_100005 | Iris Wilkerson | 3350.00 | Sandy Howard |
| 2019-10-09 | #ORD_100006 | Clifton Christensen | 3570.50 | Sandy Howard |
| 2019-10-09 | #ORD_100007 | Francis Lewis | 1280.50 | Erick Little |
| 2019-10-10 | #ORD_100008 | William Barnett | 1880.00 | Erick Little |
| 2019-11-02 | #ORD_100009 | William Barnett | 2300.50 | Sandy Howard |
| 2019-11-02 | #ORD_100010 | Francis Lewis | 2580.00 | Erick Little |
| 2019-11-03 | #ORD_100011 | Iris Wilkerson | 1240.50 | Ronald Houston |
| 2019-11-04 | #ORD_100012 | Francis Lewis | 3400.00 | Erick Little |
| 2019-11-05 | #ORD_100013 | Iris Wilkerson | 3200.00 | Sandy Howard |
| 2019-11-08 | #ORD_100014 | William Barnett | 3890.00 | Erick Little |
Example 1: GROUP BY with SUM()
The following SQL query calculates the total amounts by each customer:
SELECT o.customer, SUM(o.amount) AS TotalAmount
FROM orders o GROUP BY o.customer;
Example 2: GROUP BY with COUNT()
The following SQL query counts the number of orders by each customer:
SELECT o.customer, COUNT(o.customer) AS NumberOfOrders
FROM orders o GROUP BY o.customer;
Example 3: GROUP BY with COUNT() and SUM()
The following example calculates the total sales and counts the number of sales by each salesperson:
SELECT o.salesperson, SUM(o.amount) AS TotalAmount, COUNT(o.salesperson) AS NumberOfSales
FROM orders o GROUP BY o.salesperson;
Example 4: GROUN BY with COUNT() and SUM() for a single salesperson
The following example calculates the total sales and counts the number of sales by salesperson named Erick Little:
SELECT o.salesperson, SUM(o.amount) AS TotalAmount, COUNT(o.salesperson) AS NumberOfSales
FROM orders o
WHERE o.salesperson = "Erick Little"
GROUP BY o.salesperson;
The above SQL query is an example of how to use the WHERE clause with the GROUP BY clause.
Note that the WHERE clause must be written before the GROUP BY clause.
Example 5: GROUP BY with HAVING clause
The following SQL query returns only records that the number of sales is between 1 and 5 by each salesperson:
SELECT o.salesperson, SUM(o.amount) AS TotalAmount, COUNT(o.salesperson) AS NumberOfSales
FROM orders o
GROUP BY o.salesperson
HAVING NumberOfSales BETWEEN 1 AND 5;
The above SQL query is an example of how to use the HAVING clause with the GROUP BY clause.
Note that the HAVING clause must be written after the GROUP BY clause.
Example 6: GROUP BY with WHERE and HAVING clauses
The following example shows how to use both WHERE and HAVING clauses:
SELECT o.salesperson, SUM(o.amount) AS TotalAmount, COUNT(o.salesperson) AS NumberOfSales
FROM orders o
WHERE o.order_date BETWEEN "2019-10-01" AND "2019-11-30"
GROUP BY o.salesperson
HAVING NumberOfSales BETWEEN 1 AND 10;
Example 7: GROUP BY with ORDER BY
You can use ORDER BY with the GROUP BY clause, as the following example shows:
SELECT o.salesperson, SUM(o.amount) AS TotalAmount, COUNT(o.salesperson) AS NumberOfSales
FROM orders o
WHERE o.order_date BETWEEN "2019-10-01" AND "2019-11-30"
GROUP BY o.salesperson
HAVING NumberOfSales BETWEEN 1 AND 10
ORDER BY TotalAmount DESC;
In this tutorial, you've learned how to use the MySQL GROUP BY.
GROUP BY is used to group a set of records into summary records and is often used with aggregate functions such as COUNT(), SUM(), or AGV().