MySQL GROUP BY & HAVING: Complete Guide to Data Grouping and Reports

Introduction

When working with MySQL databases, sometimes we don't want to see every individual record.

Instead, we may want to group similar records together and generate a summary report.

For example:

• How many products are available in each category?

• What is the total sales amount for each city?

• How many customers belong to each state?

• What is the average salary for each department?

• Which categories have more than 10 products?

• Which cities generated sales above ₹1,00,000?

This is where MySQL's GROUP BY and HAVING clauses become very useful.

GROUP BY is used to divide rows into groups based on one or more columns.

HAVING is used to filter those groups based on a condition.

For example:

SELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category;

This query groups products by category and calculates the number of products in each category.

In this tutorial, we will learn:

• What GROUP BY is

• How GROUP BY works

• GROUP BY syntax

• GROUP BY with aggregate functions

• Grouping by multiple columns

• WHERE with GROUP BY

• What HAVING is

• HAVING syntax

• WHERE vs HAVING

• GROUP BY + HAVING

• Real-world reporting examples

• Common beginner mistakes

• Practice exercises

---

1. What is GROUP BY in MySQL?

The GROUP BY clause is used to group rows that have the same value in one or more columns.

Suppose we have a products table:

id	product_name	category	price
1	Laptop	Electronics	55000
2	Mobile	Electronics	25000
3	Keyboard	Accessories	1500
4	Mouse	Accessories	800
5	Monitor	Electronics	12000

If we want to know how many products belong to each category, we can use:

SELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category;

Result:

category	total_products
Accessories	2
Electronics	3

Instead of displaying every product, MySQL creates groups based on the category column.

---

2. Why Do We Use GROUP BY?

GROUP BY is mainly used when creating summary reports.

For example, an e-commerce website may need:

Electronics → 150 Products
Accessories → 85 Products
Clothing   → 230 Products
Furniture  → 70 Products

A sales application may need:

Delhi       → ₹2,50,000
Mumbai      → ₹3,20,000
Bangalore   → ₹2,10,000
Hyderabad   → ₹1,80,000

A company may need:

IT Department       → 25 Employees
HR Department       → 10 Employees
Sales Department    → 35 Employees
Accounts Department → 15 Employees

All of these reports can be created using GROUP BY.

---

3. Basic GROUP BY Syntax

The basic syntax is:

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

For example:

SELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category;

Here:

category

is the column used for grouping.

And:

COUNT(*)

calculates the number of records in each group.

---

4. Simple GROUP BY Example

Suppose we have a customers table:

id	name	city
1	Rahul	Delhi
2	Amit	Mumbai
3	Priya	Delhi
4	Neha	Bangalore
5	Ravi	Mumbai

We want to know how many customers are from each city.

Query:

SELECT city, COUNT(*) AS total_customers
FROM customers
GROUP BY city;

Result:

city	total_customers
Bangalore	1
Delhi	2
Mumbai	2

MySQL creates one group for each unique city.

---

5. GROUP BY with COUNT()

One of the most common uses of GROUP BY is with COUNT().

For example:

SELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category;

This answers:

How many products are there in each category?

Another example:

SELECT city, COUNT(*) AS total_customers
FROM customers
GROUP BY city;

This answers:

How many customers are there in each city?

---

6. GROUP BY with SUM()

You can use SUM() with GROUP BY to calculate totals for each group.

Suppose we have an orders table:

id	city	amount
1	Delhi	1500
2	Mumbai	2500
3	Delhi	3000
4	Mumbai	4000
5	Bangalore	2000

We want to calculate total sales for each city.

SELECT
    city,
    SUM(amount) AS total_sales
FROM orders
GROUP BY city;

Result:

city	total_sales
Bangalore	2000
Delhi	4500
Mumbai	6500

This is a very common business reporting query.

---

7. GROUP BY with AVG()

You can also calculate averages for each group.

Suppose we have an employees table:

id	name	department	salary
1	Rahul	IT	50000
2	Amit	IT	60000
3	Priya	HR	40000
4	Neha	HR	45000

To calculate the average salary for each department:

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Result:

department	average_salary
HR	42500
IT	55000

This can be useful for HR reports.

---

8. GROUP BY with MIN()

You can find the minimum value within each group.

For example, suppose products belong to different categories.

SELECT
    category,
    MIN(price) AS minimum_price
FROM products
GROUP BY category;

Result:

category	minimum_price
Accessories	500
Electronics	12000
Clothing	799

This tells us the cheapest product price in each category.

---

9. GROUP BY with MAX()

Similarly, you can find the maximum value within each group.

SELECT
    category,
    MAX(price) AS maximum_price
FROM products
GROUP BY category;

Result:

category	maximum_price
Accessories	2500
Electronics	55000
Clothing	4999

This tells us the most expensive product in each category.

---

10. Using Multiple Aggregate Functions with GROUP BY

You can use multiple aggregate functions in the same query.

For example:

SELECT
    category,
    COUNT(*) AS total_products,
    SUM(price) AS total_value,
    AVG(price) AS average_price,
    MIN(price) AS minimum_price,
    MAX(price) AS maximum_price
FROM products
GROUP BY category;

Result:

category	total_products	total_value	average_price	minimum_price	maximum_price
Accessories	20	45000	2250	500	5000
Electronics	15	650000	43333	12000	90000
Clothing	30	180000	6000	799	15000

This creates a complete category-level report.

---

11. GROUP BY with WHERE

WHERE can be used before GROUP BY to filter individual rows.

Suppose we only want active products.

SELECT
    category,
    COUNT(*) AS total_products
FROM products
WHERE status = 'Active'
GROUP BY category;

The process is:

1. Find Active products
2. Group them by category
3. Count products in each category

For example:

category	total_products
Electronics	12
Accessories	8
Clothing	20

Only active products are included in the grouping.

---

12. WHERE + GROUP BY Real-Time Example

Suppose an e-commerce website has an orders table:

id	city	amount	status
1	Delhi	1500	Completed
2	Mumbai	2500	Completed
3	Delhi	3000	Cancelled
4	Mumbai	4000	Completed
5	Delhi	2000	Completed

We want total completed sales by city.

Query:

SELECT
    city,
    SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY city;

Result:

city	total_sales
Delhi	3500
Mumbai	6500

The cancelled order is removed before the grouping happens.

---

13. What is HAVING in MySQL?

The HAVING clause is used to filter grouped results.

This is an important difference:

WHERE  → Filters rows
HAVING → Filters groups

For example, suppose we want to find cities that have more than 10 customers.

We can write:

SELECT
    city,
    COUNT(*) AS total_customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 10;

Here MySQL:

1. Groups customers by city.

2. Counts customers in each city.

3. Keeps only cities where the count is greater than 10.

---

14. Basic HAVING Syntax

The basic syntax is:

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

Example:

SELECT
    category,
    COUNT(*) AS total_products
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

This returns only categories containing more than 5 products.

---

15. GROUP BY and HAVING Together

GROUP BY creates groups.

HAVING filters those groups.

For example:

SELECT
    category,
    COUNT(*) AS total_products
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

Suppose the grouped result is:

category	total_products
Electronics	25
Accessories	8
Clothing	15
Furniture	4

After applying:

HAVING COUNT(*) > 10

the result becomes:

category	total_products
Electronics	25
Clothing	15

Only groups satisfying the condition remain.

---

16. HAVING with SUM()

You can filter groups based on total values.

Suppose we want cities where total sales are greater than ₹1,00,000.

SELECT
    city,
    SUM(amount) AS total_sales
FROM orders
GROUP BY city
HAVING SUM(amount) > 100000;

This returns only cities whose total sales exceed ₹1,00,000.

This is very useful for sales reports.

---

17. HAVING with AVG()

You can also filter groups based on average values.

For example, find departments where the average salary is greater than ₹50,000.

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Result may look like:

department	average_salary
IT	65000
Management	80000

Departments with an average salary of ₹50,000 or less are excluded.

---

18. HAVING with MIN() and MAX()

HAVING can also be used with MIN() and MAX().

For example, find categories where the maximum product price is greater than ₹50,000:

SELECT
    category,
    MAX(price) AS maximum_price
FROM products
GROUP BY category
HAVING MAX(price) > 50000;

Another example:

SELECT
    category,
    MIN(price) AS minimum_price
FROM products
GROUP BY category
HAVING MIN(price) < 1000;

This returns categories containing at least one product priced below ₹1,000.

---

19. WHERE vs HAVING

This is one of the most important concepts in this tutorial.

WHERE

WHERE filters individual rows before grouping.

Example:

SELECT
    city,
    COUNT(*) AS total_customers
FROM customers
WHERE status = 'Active'
GROUP BY city;

Here WHERE removes inactive customers before grouping.

HAVING

HAVING filters groups after grouping.

Example:

SELECT
    city,
    COUNT(*) AS total_customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 10;

Here MySQL first creates city groups and then removes groups having 10 or fewer customers.

---

20. WHERE and HAVING Together

You can use both WHERE and HAVING in the same query.

For example:

SELECT
    city,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY city
HAVING SUM(amount) > 50000;

What happens here?

Step 1: WHERE

Only completed orders are selected.

Step 2: GROUP BY

Orders are grouped by city.

Step 3: COUNT() and SUM()

The number of orders and total sales are calculated for each city.

Step 4: HAVING

Only cities with total sales greater than ₹50,000 are returned.

This pattern is extremely useful for business reporting.

---

21. Real-Time E-Commerce Example

Imagine an e-commerce website with thousands of orders.

Management wants to know:

Which cities generated more than ₹1,00,000 in completed sales?

Query:

SELECT
    city,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY city
HAVING SUM(amount) > 100000;

Possible result:

city	total_orders	total_sales
Mumbai	120	250000
Delhi	95	180000
Bangalore	85	150000

This is a practical reporting query.

---

22. Real-Time Customer Report

Suppose a CRM system stores customer information.

Management wants to know which cities have more than 100 customers.

Query:

SELECT
    city,
    COUNT(*) AS total_customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 100;

Result:

city	total_customers
Delhi	250
Mumbai	180
Bangalore	145

This can help businesses understand customer distribution.

---

23. Real-Time Product Category Report

Suppose an online store wants to identify categories with more than 50 products.

SELECT
    category,
    COUNT(*) AS total_products
FROM products
GROUP BY category
HAVING COUNT(*) > 50;

This can help the inventory team identify large product categories.

---

24. Real-Time Employee Department Report

Suppose a company wants to find departments with more than 20 employees.

SELECT
    department,
    COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 20;

Possible result:

department	total_employees
IT	45
Sales	35
Support	28

This is useful for HR and organizational reports.

---

25. GROUP BY Multiple Columns

You can group records using more than one column.

For example, suppose an orders table contains:

id	city	payment_method	amount
1	Delhi	UPI	1500
2	Delhi	Card	2000
3	Mumbai	UPI	3000
4	Delhi	UPI	2500
5	Mumbai	Card	4000

You can group by both city and payment method:

SELECT
    city,
    payment_method,
    SUM(amount) AS total_sales
FROM orders
GROUP BY city, payment_method;

Result:

city	payment_method	total_sales
Delhi	Card	2000
Delhi	UPI	4000
Mumbai	Card	4000
Mumbai	UPI	3000

Now MySQL creates groups based on the combination of both columns.

---

26. GROUP BY Multiple Columns with COUNT()

Another example:

SELECT
    city,
    status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY city, status;

This can produce:

city	status	total_orders
Delhi	Completed	50
Delhi	Cancelled	5
Mumbai	Completed	70
Mumbai	Cancelled	8

This provides a more detailed report.

---

27. GROUP BY with HAVING on Multiple Columns

You can also use HAVING with multiple grouping columns.

SELECT
    city,
    payment_method,
    SUM(amount) AS total_sales
FROM orders
GROUP BY city, payment_method
HAVING SUM(amount) > 50000;

This returns only city/payment combinations whose total sales exceed ₹50,000.

---

28. GROUP BY with ORDER BY

After creating grouped results, you can sort them using ORDER BY.

For example, find sales by city and display the highest sales first:

SELECT
    city,
    SUM(amount) AS total_sales
FROM orders
GROUP BY city
ORDER BY total_sales DESC;

Result:

city	total_sales
Mumbai	250000
Delhi	180000
Bangalore	150000

Here GROUP BY creates the city-level summary and ORDER BY sorts the final result.

---

29. GROUP BY + HAVING + ORDER BY

You can combine all three.

For example:

SELECT
    city,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY city
HAVING SUM(amount) > 50000
ORDER BY total_sales DESC;

This query:

1. Selects completed orders.

2. Groups them by city.

3. Calculates order count and sales.

4. Removes cities with sales of ₹50,000 or less.

5. Sorts remaining cities from highest sales to lowest.

This is a very useful business reporting pattern.

---

30. Understanding the SQL Query Order

When writing queries with WHERE, GROUP BY, HAVING, and ORDER BY, beginners often get confused about the order.

The common structure is:

SELECT
    column_name,
    aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
HAVING group_condition
ORDER BY column_name;

For example:

SELECT
    city,
    SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY city
HAVING SUM(amount) > 50000
ORDER BY total_sales DESC;

Remember this general pattern:

FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY

The exact internal SQL execution process is more nuanced, but this mental model is useful for understanding how these clauses work together.

---

31. Common GROUP BY Mistakes

Mistake 1: Forgetting GROUP BY

Incorrect:

SELECT category, COUNT(*)
FROM products;

If you want a count for each category, you need:

SELECT
    category,
    COUNT(*) AS total_products
FROM products
GROUP BY category;

---

32. Mistake 2: Using WHERE with Aggregate Conditions

Beginners sometimes write:

SELECT
    city,
    COUNT(*) AS total_customers
FROM customers
WHERE COUNT(*) > 10
GROUP BY city;

This is incorrect because WHERE is not used to filter aggregate results.

Use HAVING:

SELECT
    city,
    COUNT(*) AS total_customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 10;

---

33. Mistake 3: Confusing WHERE and HAVING

Remember:

WHERE  → Filters rows
HAVING → Filters groups

Example:

WHERE status = 'Completed'

filters individual orders.

While:

HAVING SUM(amount) > 50000

filters grouped sales results.

---

34. Mistake 4: Forgetting Multiple GROUP BY Columns

Suppose you want a report based on both city and payment method.

Incorrect:

SELECT city, payment_method, SUM(amount)
FROM orders
GROUP BY city;

The grouping does not include payment_method.

Use:

SELECT
    city,
    payment_method,
    SUM(amount) AS total_sales
FROM orders
GROUP BY city, payment_method;

---

35. Mistake 5: Using HAVING Without Understanding the Group

For example:

SELECT
    city,
    COUNT(*) AS total_customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 100;

This means:

Return cities where the number of customers is greater than 100.

It does not mean:

Return individual customers whose count is greater than 100.

Always remember that HAVING works on grouped results.

---

36. Practical Business Reporting Query

Suppose an e-commerce company wants this report:

Show completed orders by city, including the number of orders and total sales. Only show cities with more than 20 orders, and display the highest sales first.

Query:

SELECT
    city,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY city
HAVING COUNT(*) > 20
ORDER BY total_sales DESC;

This single query performs multiple reporting operations.

It:

• Filters completed orders

• Groups orders by city

• Counts orders

• Calculates total sales

• Filters cities with more than 20 orders

• Sorts by highest sales

This is exactly the kind of SQL query used in real-world dashboards and reporting systems.

---

37. GROUP BY Quick Reference

Group records by one column

SELECT category, COUNT(*)
FROM products
GROUP BY category;

Group and calculate total

SELECT city, SUM(amount)
FROM orders
GROUP BY city;

Group and calculate average

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Filter rows before grouping

SELECT city, COUNT(*)
FROM customers
WHERE status = 'Active'
GROUP BY city;

Filter groups after grouping

SELECT city, COUNT(*)
FROM customers
GROUP BY city
HAVING COUNT(*) > 10;

Group using multiple columns

SELECT city, payment_method, SUM(amount)
FROM orders
GROUP BY city, payment_method;

Group + filter + sort

SELECT
    city,
    SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY city
HAVING SUM(amount) > 50000
ORDER BY total_sales DESC;

---

38. WHERE vs HAVING Quick Reference

Feature	WHERE	HAVING
Filters	Individual rows	Groups
Used before grouping	Yes	No
Used after grouping	No	Yes
Common use	Filter records	Filter aggregate results
Example	status = 'Active'	COUNT(*) > 10
Aggregate condition	Generally not used	Commonly used

Easy way to remember:

WHERE  → Which rows?
HAVING → Which groups?

---

39. Practice Exercises

Try solving these queries yourself.

Exercise 1

Find the number of products in each category.

-- Write your query here

Exercise 2

Find the number of customers in each city.

-- Write your query here

Exercise 3

Find the total sales for each city.

-- Write your query here

Exercise 4

Find the average salary for each department.

-- Write your query here

Exercise 5

Find categories having more than 10 products.

-- Write your query here

Exercise 6

Find cities where total sales are greater than ₹1,00,000.

-- Write your query here

Exercise 7

Find departments where the average salary is greater than ₹50,000.

-- Write your query here

Exercise 8

Find the total number of completed orders for each city.

-- Write your query here

Exercise 9

Find cities having more than 20 completed orders.

-- Write your query here

Exercise 10

Create a report containing:

City
Total Orders
Total Sales

Show only cities having total sales above ₹50,000 and sort them from highest sales to lowest.

---

40. Practical Query Challenge

Imagine you are developing an e-commerce admin dashboard.

Your orders table contains:

id
customer_id
city
amount
status
created_at

The business owner asks:

"Show me the cities where we have completed more than 50 orders and generated more than ₹1,00,000 in sales. Sort the cities by highest sales."

Think about the query step by step.

Step 1: We only need completed orders

WHERE status = 'Completed'

Step 2: We need results by city

GROUP BY city

Step 3: We need order count and sales

COUNT(*)
SUM(amount)

Step 4: We need to filter groups

HAVING COUNT(*) > 50
AND SUM(amount) > 100000

Step 5: We need highest sales first

ORDER BY total_sales DESC

Complete query:

SELECT
    city,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY city
HAVING COUNT(*) > 50
   AND SUM(amount) > 100000
ORDER BY total_sales DESC;

This is a practical example of how WHERE, GROUP BY, HAVING, and ORDER BY can work together to generate a business report.

---

41. Important Things to Remember

Before moving to the next MySQL topic, remember these points:

1. GROUP BY is used to create groups of similar records.

2. GROUP BY is commonly used with aggregate functions.

3. COUNT() can count records in each group.

4. SUM() can calculate totals for each group.

5. AVG() can calculate averages for each group.

6. MIN() can find the minimum value in each group.

7. MAX() can find the maximum value in each group.

8. WHERE filters individual rows before grouping.

9. HAVING filters groups after grouping.

10. You can use WHERE and HAVING together.

11. You can group by multiple columns.

12. ORDER BY can sort the grouped results.

13. GROUP BY is extremely useful for business reports and dashboards.

---

Conclusion

MySQL GROUP BY and HAVING are essential when working with reports and summarized data.

GROUP BY allows you to organize records into groups:

GROUP BY category

While HAVING allows you to filter those groups:

HAVING COUNT(*) > 10

A common real-world pattern is:

SELECT
    city,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY city
HAVING SUM(amount) > 100000
ORDER BY total_sales DESC;

This type of query can be used for:

• Sales reports

• E-commerce dashboards

• Customer analytics

• Employee reports

• Inventory reports

• Business intelligence

• Admin panels

• PHP applications

• CodeIgniter applications

The key concept to remember is:

WHERE  → Filter rows
GROUP BY → Create groups
HAVING → Filter groups
ORDER BY → Sort results

Once you understand these concepts, you can create much more powerful reporting queries in MySQL.

---

Next MySQL Tutorial

In the next tutorial, we will learn:

MySQL JOIN Tutorial: INNER JOIN, LEFT JOIN, RIGHT JOIN with Real Examples

We will learn how to retrieve related data from multiple tables and understand how different types of joins work with practical database examples.