MySQL GROUP BY Clause with Examples


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_dateorder_numbercustomeramountsalesperson
2019-10-07#ORD_100001Lisa Ramsey1980.00Erick Little
2019-10-07#ORD_100002Lisa Ramsey1350.50Ronald Houston
2019-10-07#ORD_100003Iris Wilkerson1230.40Ronald Houston
2019-10-08#ORD_100004Clifton Christensen1300.00Erick Little
2019-10-08#ORD_100005Iris Wilkerson3350.00Sandy Howard
2019-10-09#ORD_100006Clifton Christensen3570.50Sandy Howard
2019-10-09#ORD_100007Francis Lewis1280.50Erick Little
2019-10-10#ORD_100008William Barnett1880.00Erick Little
2019-11-02#ORD_100009William Barnett2300.50Sandy Howard
2019-11-02#ORD_100010Francis Lewis2580.00Erick Little
2019-11-03#ORD_100011Iris Wilkerson1240.50Ronald Houston
2019-11-04#ORD_100012Francis Lewis3400.00Erick Little
2019-11-05#ORD_100013Iris Wilkerson3200.00Sandy Howard
2019-11-08#ORD_100014William Barnett3890.00Erick 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().

Leave a Reply

Your email address will not be published. Required fields are marked *