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;

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;

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

SELECT * FROM orders;

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;