The Code Developer

MySQL GROUP BY

Introduction to MYSQL GROUP BY clause

The MYSQL GROUP BY Clause is used to collect data from multiple records and group the result by one or more column.

The GROUP BY statement is often used with aggregate functions such as SUM, AVG, MAX, MIN and COUNT. It is generally used in a SELECT statement.

GROUP BY Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

To understand MySQL GROUP BY clause , consider an “customers” table, which is having the following records −

SELECT * FROM customers;
CustomerIDCustomerNameAgeCityCountry
1Christina40LondonUnited Kingdom
2Maria Anders56BerlinGermany
3Matthew34LuleåSweden
4Alen51BerlinGermany
5Jacob28México D.FMexico
6Aastha26ChandigarhIndia
7Joshua21México D.FMexico

Now suppose based on the above table you want to count the number of customers in each country, then you can do it as follows −

SELECT COUNT(CustomerID) AS "Number of customers", Country 
FROM customers 
GROUP BY Country;
Number of customersCountry
2Germany
1India
2Mexico
1Sweden
1United Kingdom

Below is a selection from the “orders” table, which is having the following records −

SELECT * FROM orders;
OrderIDCustomerIDOrderDate
122017-02-02
222017-03-23
342017-06-14
452017-06-17
562017-07-28
662017-07-31
772017-09-10
862017-09-12

if you want to count number of orders per customer, then it can be done as follows −

SELECT CustomerName, COUNT(orders.OrderID) AS TotalOrder
FROM customers
INNER JOIN Orders 
ON customers.CustomerID = orders.CustomerID
GROUP BY customers.CustomerID
ORDER BY TotalOrder DESC;
CustomerNameTotalOrder
Aastha3
Maria Anders2
Joshua1
Alen1
Jacob1