• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL HAVING

← Previous Next →

The MySQL HAVING clause is used to filter grouped data after performing aggregate calculations. While the WHERE clause filters individual rows before aggregation, HAVING is applied after the rows have been grouped using GROUP BY.

For example, if you want to find products with total sales above a certain amount or customers who have placed more than two orders, MySQL HAVING allows you to achieve this efficiently. It is particularly useful in generating reports and performing analytics on large datasets.

What Is the HAVING Clause?

In MySQL, HAVING is a SQL clause that restricts the output of a GROUP BY query based on aggregate calculations.

Key Points:

  • Works only with aggregated data.

  • Filters groups, not individual rows.

  • Often used with aggregate functions like SUM(), COUNT(), AVG(), MAX(), MIN().

  • Can include logical conditions with AND and OR.

HAVING Syntax

The basic syntax of the HAVING clause is:

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING aggregate_function(column2) condition;

Explanation:

  • WHERE filters rows before grouping.

  • GROUP BY groups the rows based on specified columns.

  • HAVING filters the grouped data after aggregation.

Example Table

Consider the following orders table:

orders

order_id customer_name product quantity amount
101 Ananya Laptop 2 50000
102 Priya Laptop 1 25000
103 Isha Keyboard 5 5000
104 Riya Laptop 3 75000
105 Ananya Keyboard 2 2000
106 Sunita Mouse 4 400
107 Priya Keyboard 1 1000

Basic HAVING Example

To find products with total sales greater than 10,000:

SELECT product, SUM(amount) AS total_sales
FROM orders
GROUP BY product
HAVING SUM(amount) > 10000;

Result:

product total_sales
Laptop 150000

Explanation:

  • GROUP BY product groups rows by product.

  • SUM(amount) calculates total sales per product.

  • HAVING SUM(amount) > 10000 filters products with total sales above 10,000.

HAVING with COUNT()

To find customers who have placed more than one order:

SELECT customer_name, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_name
HAVING COUNT(order_id) > 1;

Result:

customer_name total_orders
Ananya 2
Priya 2

Explanation:

  • COUNT(order_id) counts orders per customer.

  • HAVING COUNT(order_id) > 1 ensures only customers with multiple orders appear.

HAVING with Multiple Aggregate Functions

You can combine multiple aggregate functions in HAVING. Example: Customers who have placed more than one order and whose total purchase exceeds 20,000:

SELECT customer_name, COUNT(order_id) AS total_orders, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_name
HAVING COUNT(order_id) > 1 AND SUM(amount) > 20000;

Result:

customer_name total_orders total_amount
Ananya 2 52000

Explanation:

  • Both conditions are applied after aggregation.

  • Only groups meeting both criteria are included.

HAVING with Multiple Columns

You can group by multiple columns and filter using HAVING. Example: Total quantity per customer per product greater than 2:

SELECT customer_name, product, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_name, product
HAVING SUM(quantity) > 2;

Result:

customer_name product total_quantity
Ananya Laptop 2
Isha Keyboard 5
Riya Laptop 3
Sunita Mouse 4

Explanation:

  • Grouping by customer_name and product creates subgroups.

  • HAVING SUM(quantity) > 2 filters out smaller quantities.

HAVING with WHERE Clause

You can use WHERE to filter rows before grouping. Example: Find customers with total orders over 1, considering only orders above 1000:

SELECT customer_name, COUNT(order_id) AS total_orders, SUM(amount) AS total_amount
FROM orders
WHERE amount > 1000
GROUP BY customer_name
HAVING COUNT(order_id) > 1;

Result:

customer_name total_orders total_amount
Ananya 2 52000
Priya 1 25000

Explanation:

  • WHERE amount > 1000 filters individual rows first.

  • Then GROUP BY groups the remaining rows.

  • HAVING COUNT(order_id) > 1 filters groups with more than one order.

HAVING with ORDER BY

You can sort results after filtering groups:

SELECT product, SUM(amount) AS total_sales
FROM orders
GROUP BY product
HAVING SUM(amount) > 10000
ORDER BY total_sales DESC;

Result:

product total_sales
Laptop 150000

Explanation:

  • ORDER BY sorts grouped and filtered results, showing the highest total sales first.

Practical Real-World Scenarios

  1. Sales Reports: Find products with total revenue exceeding a target.

  2. Customer Analysis: Identify high-value customers with multiple orders.

  3. Inventory Management: Track products sold in quantities above a threshold.

  4. Employee Performance: Find employees who have completed more than a certain number of tasks.

Common Mistakes to Avoid

  1. Using WHERE for aggregates:
    Aggregate functions cannot be used in WHERE. Use HAVING instead.

  2. Not including all non-aggregated columns in GROUP BY:
    Any column in SELECT that is not aggregated must appear in GROUP BY.

  3. Confusing HAVING and WHERE:
    Remember: WHERE filters rows, HAVING filters groups.

  4. Overusing HAVING without aggregation:
    HAVING is intended for filtering aggregated results; using it on individual rows may be unnecessary.

Summary of the Tutorial

The MySQL HAVING clause is crucial for filtering aggregated data after grouping.

  • It complements GROUP BY for advanced reporting.

  • Supports aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN().

  • Can combine multiple conditions using AND / OR.

  • Aliases can be used in HAVING for better readability.

  • Works alongside WHERE to filter both rows and groups efficiently.

Understanding HAVING is essential for performing data analysis and generating meaningful summaries from large datasets in MySQL.

Practice Questions

  1. Write a query to find all products with total sales greater than 20,000 using GROUP BY and HAVING.

  2. Using HAVING, display customers who have placed more than 2 orders in the orders table.

  3. Write a query to show products with total quantity sold exceeding 5.

  4. Using GROUP BY and HAVING, find customers whose total purchase amount is more than 50,000.

  5. Write a query to display customers with more than one order and whose total sales exceed 10,000.

  6. Using HAVING, show all products where the average order amount is greater than 10,000.

  7. Write a query to display the total quantity per customer per product greater than 3.

  8. Using HAVING, find employees who have completed more than 5 tasks (assume tasks table with employee_id and task_count).

  9. Write a query to show customers with the maximum single order amount greater than 25,000.

  10. Using GROUP BY and HAVING, list all products whose total sales are less than 10,000.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top