In MySQL, aggregation and filtering of grouped data is often done using the GROUP BY and HAVING clauses. This guide will help you understand these concepts with practical examples.
Understanding GROUP BY Clause
The GROUP BY clause groups rows with the same values in specified columns into summary rows. It's often used with aggregate functions like COUNT(), MAX(), MIN(), SUM(), and AVG().
Basic Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;1. Simple Grouping
Example: Counting Records
To count the number of orders each customer has placed:
SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID;This query groups the orders by CustomerID and counts the number of orders for each customer.
2. Grouping by Multiple Columns
Example: Grouping by More Than One Column
To find the total sales for each product in each store:
SELECT StoreID, ProductID, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY StoreID, ProductID;This groups the sales data first by StoreID, then by ProductID.
Understanding HAVING Clause
The HAVING clause is used to filter groups created by GROUP BY based on a specified condition. It's similar to WHERE, but HAVING works on aggregated data.
Basic Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
HAVING group_condition;3. Filtering Groups with HAVING
Example: Filtering Groups
To find customers who have placed more than 5 orders:
SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 5;This query groups the orders by CustomerID and includes only those groups where the count of orders is greater than 5.
Example: HAVING with Multiple Conditions
To find products with an average rating above 4 and more than 10 reviews:
SELECT ProductID, AVG(Rating) AS AverageRating, COUNT(ReviewID) AS NumberOfReviews
FROM Reviews
GROUP BY ProductID
HAVING AVG(Rating) > 4 AND COUNT(ReviewID) > 10;4. Combining WHERE, GROUP BY, and HAVING
Example: Using WHERE with GROUP BY and HAVING
To find stores with sales in 2021 where the total sales exceeded $100,000:
SELECT StoreID, SUM(Sales) AS TotalSales
FROM Sales
WHERE Year = 2021
GROUP BY StoreID
HAVING SUM(Sales) > 100000;This query filters records from 2021 (WHERE), groups them by StoreID, and then applies the HAVING condition on the grouped data.