When working with database records, you often need more than individual rows. You may need to calculate total sales, average prices, record counts, minimum or maximum values, or summaries for each category.
MySQL provides aggregate functions, GROUP BY, and HAVING to summarize and analyze groups of records.
Learning Objectives
Understand how GROUP BY works
Use common aggregate functions
Calculate totals, averages, counts, minimums, and maximums
Understand the difference between WHERE and HAVING
Filter grouped results using HAVING
Group records using multiple columns
Sort grouped results
Use DISTINCT with grouped data
Creating Sample Data
For the examples, create a new table named SalesData.
USE trainingdbc;
CREATE TABLE SalesData (
sale_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
region VARCHAR(50),
quantity INT,
sales_amount DECIMAL(10,2)
);
Insert sample records:
INSERT INTO SalesData
(product_name, category, region, quantity, sales_amount)
VALUES
('Laptop', 'Electronics', 'Doha', 2, 7000.00),
('Monitor', 'Electronics', 'Doha', 3, 2550.00),
('Keyboard', 'Accessories', 'Doha', 5, 750.00),
('Mouse', 'Accessories', 'Al Rayyan', 10, 750.00),
('Printer', 'Electronics', 'Al Rayyan', 2, 1300.00),
('Keyboard', 'Accessories', 'Al Rayyan', 4, 600.00);
Check the records:
SELECT * FROM SalesData;
Introduction to GROUP BY
GROUP BY combines rows that contain the same value in one or more columns.
For example, to group records by category:
SELECT category
FROM SalesData
GROUP BY category;
The result contains:
Electronics
Accessories
The real value of GROUP BY becomes clearer when it is combined with aggregate functions.
Aggregate Functions
Aggregate functions perform calculations across multiple rows and return summarized values.
Common functions include:
COUNT() – counts records
SUM() – calculates a total
AVG() – calculates an average
MIN() – finds the minimum value
MAX() – finds the maximum value
COUNT()
To count the number of sales records:
SELECT COUNT(*) AS TotalSales
FROM SalesData;
The result is:
6
SUM()
To calculate total sales:
SELECT SUM(sales_amount) AS TotalSalesAmount
FROM SalesData;
AVG()
To calculate the average sales amount:
SELECT AVG(sales_amount) AS AverageSales
FROM SalesData;
MIN() and MAX()
To identify the lowest and highest sales amounts:
SELECT
MIN(sales_amount) AS MinimumSale,
MAX(sales_amount) AS MaximumSale
FROM SalesData;
GROUP BY with Aggregate Functions
Now group the records by category and calculate total sales:
SELECT
category,
SUM(sales_amount) AS TotalSales
FROM SalesData
GROUP BY category;
The result should be:
category | TotalSales |
Accessories | 2100.00 |
Electronics | 10850.00 |
This provides a summary for each product category instead of displaying individual sales records.
You can also calculate multiple values:
SELECT
category,
COUNT(*) AS NumberOfSales,
SUM(sales_amount) AS TotalSales,
AVG(sales_amount) AS AverageSales
FROM SalesData
GROUP BY category;
WHERE vs HAVING
Both WHERE and HAVING filter information, but they are applied at different stages of a query.
WHERE filters individual rows before grouping.
HAVING filters grouped results after GROUP BY.
For example:
SELECT
category,
SUM(sales_amount) AS TotalSales
FROM SalesData
WHERE quantity >= 3
GROUP BY category;
Here, MySQL first keeps records where quantity >= 3 and then groups those records.
By contrast:
SELECT
category,
SUM(sales_amount) AS TotalSales
FROM SalesData
GROUP BY category
HAVING SUM(sales_amount) > 3000;
Here, MySQL creates the category groups first and then returns only groups whose total sales exceed 3000.
Applying HAVING
Suppose you only want categories with total sales greater than 3000.
Run:
SELECT
category,
SUM(sales_amount) AS TotalSales
FROM SalesData
GROUP BY category
HAVING SUM(sales_amount) > 3000;
Based on the sample data, only Electronics meets this condition.
category | TotalSales |
Electronics | 10850.00 |
Grouping by Multiple Columns
MySQL can group records using more than one column.
For example:
SELECT
category,
region,
SUM(sales_amount) AS TotalSales
FROM SalesData
GROUP BY category, region;
This creates a separate group for each category and region combination.
For example:
Accessories + Doha
Accessories + Al Rayyan
Electronics + Doha
Electronics + Al Rayyan
This is useful when summaries need to be analyzed across multiple dimensions.
Sorting Grouped Data
ORDER BY can be combined with GROUP BY to sort summarized results.
For example:
SELECT
category,
SUM(sales_amount) AS TotalSales
FROM SalesData
GROUP BY category
ORDER BY TotalSales DESC;
The category with the highest total sales appears first.
You can also combine grouping, filtering, and sorting:
SELECT
category,
SUM(sales_amount) AS TotalSales
FROM SalesData
GROUP BY category
HAVING SUM(sales_amount) > 1000
ORDER BY TotalSales DESC;
GROUP BY with DISTINCT
DISTINCT removes duplicate values from a result or from the values considered by certain aggregate functions.
For example, to see unique categories:
SELECT DISTINCT category
FROM SalesData;
You can also count distinct products within each category:
SELECT
category,
COUNT(DISTINCT product_name) AS UniqueProducts
FROM SalesData
GROUP BY category;
This counts each product name only once within its category.
For example, Keyboard appears more than once in the sample data, but COUNT(DISTINCT product_name) counts it once for the Accessories category.
Combining WHERE, GROUP BY, HAVING, and ORDER BY
These clauses can be combined in a single query.
SELECT
category,
SUM(sales_amount) AS TotalSales
FROM SalesData
WHERE quantity >= 2
GROUP BY category
HAVING SUM(sales_amount) > 1000
ORDER BY TotalSales DESC;
The query works logically as follows:
WHERE filters individual records.
GROUP BY organizes the remaining records into categories.
SUM() calculates total sales for each category.
HAVING filters the grouped results.
ORDER BY sorts the final results.
WHERE vs HAVING Summary
Feature | WHERE | HAVING |
Filters | Individual rows | Grouped results |
Applied | Before grouping | After grouping |
Commonly used with | SELECT | GROUP BY |
Aggregate conditions | Generally not used for group aggregate filtering | Designed for aggregate conditions |
For example:
WHERE quantity > 5
filters individual records.
While:
HAVING SUM(sales_amount) > 5000
filters summarized groups.
Common Mistakes
When working with grouped data, watch for these common issues:
Selecting nonaggregated columns that are not appropriately included in GROUP BY
Using WHERE when an aggregate result needs to be filtered
Using HAVING unnecessarily for conditions that should filter rows before grouping
Forgetting to use an aggregate function when calculating summaries
Grouping by the wrong column
Sorting by the wrong aggregate result
Forgetting that DISTINCT changes which duplicate values are considered
Still need help?
Contact us