The GROUP BY clause in MySQL is used to arrange identical data into groups. It is commonly used with aggregate functions like SUM, COUNT, AVG, MAX, and MIN to perform calculations on groups of data.

Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

Northwind Database Example

We’ll use the Northwind database, which includes tables like Orders, OrderDetails, Products, and Customers.

Example 1: Total Orders by Customer

Suppose you want to find out how many orders each customer has placed.

Query

SELECT CustomerID, COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY CustomerID;

Result

Explanation

• COUNT(OrderID): Counts the total number of orders for each CustomerID.
• GROUP BY CustomerID: Groups the orders by customer.

Example 2: Total Revenue by Product

To calculate the total revenue generated by each product, we can use the OrderDetails table.

Query

SELECT ProductID, SUM(UnitPrice * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY ProductID;

Result

Explanation

• SUM(UnitPrice * Quantity): Calculates the revenue for each product.
• GROUP BY ProductID: Groups the revenue by ProductID.

Example 3: Number of Orders per Country

You can use the Orders and Customers tables to count the number of orders placed by customers in each country.

Query

SELECT c.Country, COUNT(o.OrderID) AS TotalOrders
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Country;

Result

Explanation

• JOIN: Combines Customers and Orders tables based on CustomerID.
• COUNT(o.OrderID): Counts orders placed by customers in each country.
• GROUP BY c.Country: Groups the count by country.

Example 4: Average Order Value by Employee

Find the average value of orders handled by each employee.

Query

SELECT EmployeeID, AVG(Freight) AS AverageFreight
FROM Orders
GROUP BY EmployeeID;

Result

Explanation

• AVG(Freight): Calculates the average freight for orders handled by each employee.
• GROUP BY EmployeeID: Groups the average by EmployeeID.

Using GROUP BY with HAVING

To filter grouped data, use the HAVING clause (like a WHERE clause for groups).

Example: Products with Total Revenue > 5000

SELECT ProductID, SUM(UnitPrice * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY ProductID
HAVING SUM(UnitPrice * Quantity) > 5000;

Result

Explanation

• HAVING SUM(UnitPrice * Quantity) > 5000: Filters products with revenue greater than 5000.

Key Points

1. GROUP BY Groups Rows: Groups rows that have the same values in specified columns.
2. Works with Aggregate Functions: Commonly used with COUNT, SUM, AVG, MAX, MIN.
3. Order Matters: Always comes after the WHERE clause but before the HAVING and ORDER BY clauses.
4. HAVING Filters Groups: Use HAVING to apply conditions on groups (not rows).