Filtering Aggregated Results Using HAVING in MySQL

Course: MySQL Training

Topic: Filtering Aggregated Results Using HAVING

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to HAVING in MySQL

The HAVING clause in MySQL is used to filter grouped or aggregated results based on specified conditions. It is particularly useful when working with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().

In database analysis, you may need to find departments with more than five employees, customers who have placed multiple orders, products whose total sales exceed a particular amount, or categories with an average price above a threshold. These tasks require conditions based on calculated aggregate values. The HAVING clause makes this possible.

For example, suppose an employee table contains hundreds of records. You want to display only departments whose average salary is greater than 50,000. You can group employees by department, calculate each department's average salary, and use HAVING to return only the qualifying departments.

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

The query calculates the average salary for each department and displays only departments whose average is above 50,000. The HAVING clause filters the grouped results rather than individual employee records.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand the purpose of the HAVING clause in MySQL.
• Understand the difference between WHERE and HAVING.
• Write SQL queries that filter aggregate results.
• Use HAVING with GROUP BY.
• Filter grouped results using COUNT(), SUM(), AVG(), MIN(), and MAX().
• Apply multiple conditions with AND and OR.
• Use HAVING with WHERE, ORDER BY, and LIMIT.
• Understand how aliases and NULL values affect aggregate queries.
• Identify common mistakes when filtering aggregated data.
• Create practical reports using the HAVING clause.

3. What Is the HAVING Clause?

The HAVING clause specifies a condition that groups must satisfy to appear in the final result. It is commonly used with GROUP BY and aggregate functions.

Consider a sales table containing transaction records from different cities. You want to display only cities with total sales greater than 100,000. First, MySQL groups the sales by city and calculates the total for each city. Then, HAVING filters those groups based on the total sales.

3.1 Basic Syntax

SELECT
    grouping_column,
    AGGREGATE_FUNCTION(numeric_column) AS aggregate_result
FROM table_name
GROUP BY grouping_column
HAVING AGGREGATE_FUNCTION(numeric_column) condition;

3.2 Explanation of the Syntax

4. Understanding WHERE and HAVING

The most important concept in this topic is understanding when to use WHERE and when to use HAVING. Both clauses filter data, but they serve different purposes.

4.1 WHERE Clause

The WHERE clause filters individual rows before grouping and aggregation. It is appropriate for conditions on ordinary row-level values.

SELECT *
FROM employees
WHERE department = 'IT';

This query returns only employees whose department is IT.

4.2 HAVING Clause

The HAVING clause filters groups based on aggregate results, such as a department's employee count or average salary.

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;

This query returns only departments with more than three employee records.

4.3 Comparison Table

5. Creating a Sample Database

To practise filtering aggregated results, create a database containing employee and sales information. The employee table will help demonstrate grouping, employee counts, average salaries, and department-level summaries.

5.1 Create a Database

CREATE DATABASE having_practice;

USE having_practice;

5.2 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    city VARCHAR(50)
);

5.3 Insert Sample Records

INSERT INTO employees
(employee_id, employee_name, department, salary, city)
VALUES
(1, 'Amit', 'IT', 45000.00, 'Mumbai'),
(2, 'Priya', 'HR', 40000.00, 'Mumbai'),
(3, 'Rahul', 'IT', 55000.00, 'Pune'),
(4, 'Sneha', 'Finance', 50000.00, 'Mumbai'),
(5, 'Karan', 'HR', 35000.00, 'Pune'),
(6, 'Neha', 'Finance', 60000.00, 'Mumbai'),
(7, 'Vikas', 'IT', 48000.00, 'Mumbai'),
(8, 'Pooja', 'HR', 42000.00, 'Pune'),
(9, 'Arjun', 'Finance', 52000.00, 'Pune'),
(10, 'Meera', 'IT', 58000.00, 'Mumbai'),
(11, 'Rohit', 'Marketing', 39000.00, 'Mumbai'),
(12, 'Anjali', 'Marketing', 43000.00, 'Pune');

5.4 View the Table

SELECT *
FROM employees;

Review the records and identify the different departments, salaries, and cities. These values will be used in the examples throughout this lesson.

6. Filtering Groups Using COUNT()

COUNT() is one of the most common aggregate functions used with HAVING. It counts rows or non-NULL values and allows you to filter groups based on how many records they contain.

6.1 Find Departments with More Than Three Employees

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;

MySQL groups employees by department, counts the records in each group, and displays only departments with more than three employees. In the sample data, IT has four employees, so it qualifies.

6.2 Find Departments with at Least Three Employees

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3;

This query returns departments containing three or more employee records. The condition includes groups whose count equals three as well as those with larger counts.

6.3 Find Departments with Exactly Two Employees

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) = 2;

This is useful when identifying departments with a particular team size. In the sample data, HR, Finance, and Marketing each contain two employees.

6.4 Find Cities with More Than Five Employees

SELECT
    city,
    COUNT(*) AS employee_count
FROM employees
GROUP BY city
HAVING COUNT(*) > 5;

The query groups employees by city and returns only cities with more than five records.

6.5 COUNT(*) Versus COUNT(column_name)

COUNT(*) counts all rows in a group. COUNT(column_name) counts only rows where the specified column is not NULL.

SELECT
    department,
    COUNT(*) AS total_records,
    COUNT(salary) AS employees_with_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2;

This query returns departments with at least two employee records and displays both the total number of records and the number with a non-NULL salary.

7. Filtering Groups Using SUM()

The SUM() function calculates the total of numeric values within a group. You can use HAVING to retain groups whose total exceeds or falls below a specified threshold.

7.1 Find Departments with Total Salaries Above 100,000

SELECT
    department,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

The query calculates the combined salary of employees in each department. Only departments with total salaries above 100,000 appear in the result.

7.2 Find Departments with Total Salaries Below 100,000

SELECT
    department,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) < 100000;

This query returns departments whose combined salary is less than 100,000.

7.3 Use SUM() with a Sales Table

The following example uses a sales table to calculate total revenue by product.

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    region VARCHAR(50),
    quantity INT,
    unit_price DECIMAL(10,2)
);

INSERT INTO sales
(sale_id, product_name, region, quantity, unit_price)
VALUES
(1, 'Laptop', 'West', 5, 50000.00),
(2, 'Laptop', 'East', 3, 50000.00),
(3, 'Mouse', 'West', 20, 500.00),
(4, 'Mouse', 'East', 15, 500.00),
(5, 'Keyboard', 'West', 10, 1500.00),
(6, 'Keyboard', 'East', 8, 1500.00),
(7, 'Monitor', 'West', 4, 12000.00),
(8, 'Monitor', 'East', 6, 12000.00);

Calculate revenue by product using quantity multiplied by unit price.

SELECT
    product_name,
    SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY product_name
HAVING SUM(quantity * unit_price) > 20000;

The query includes only products whose calculated total revenue exceeds 20,000.

8. Filtering Groups Using AVG()

The AVG() function calculates the average of non-NULL numeric values within a group. The HAVING clause can then filter groups based on their averages.

8.1 Departments with Average Salary Above 45,000

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

This query returns departments whose average salary is greater than 45,000. Based on the sample records, IT and Finance qualify.

8.2 Departments with Average Salary Below 45,000

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

This query displays departments whose average salary is less than 45,000.

8.3 Departments with Average Salary Between Two Values

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) BETWEEN 40000 AND 55000;

The BETWEEN operator includes both boundary values. The result contains departments whose average salary is at least 40,000 and at most 55,000.

8.4 Find Products with Average Sales Quantity Above a Threshold

SELECT
    product_name,
    AVG(quantity) AS average_quantity
FROM sales
GROUP BY product_name
HAVING AVG(quantity) > 5;

This query calculates the average quantity recorded per product and returns only products whose average quantity is greater than five units per sales record.

9. Filtering Groups Using MIN() and MAX()

The MIN() and MAX() functions identify the smallest and largest non-NULL values within a group. They can also be used in HAVING conditions.

9.1 Departments with a Maximum Salary Above 55,000

SELECT
    department,
    MAX(salary) AS maximum_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 55000;

This query returns departments with at least one salary value above 55,000.

9.2 Departments with a Minimum Salary of at Least 40,000

SELECT
    department,
    MIN(salary) AS minimum_salary
FROM employees
GROUP BY department
HAVING MIN(salary) >= 40000;

The query returns departments whose minimum non-NULL salary is at least 40,000. A department with any salary below 40,000 will not qualify.

9.3 Compare Minimum and Maximum Values

SELECT
    department,
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary
FROM employees
GROUP BY department
HAVING MAX(salary) - MIN(salary) > 10000;

This query identifies departments where the difference between the maximum and minimum salary is greater than 10,000. The condition measures the salary range within each department.

10. Using HAVING with Multiple Conditions

You can combine multiple aggregate conditions using logical operators such as AND and OR. This helps filter groups based on more than one business requirement.

10.1 Use AND

SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2
   AND AVG(salary) > 40000;

Both conditions must be true. The department must have at least two employees and an average salary above 40,000.

10.2 Use OR

SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 4
    OR AVG(salary) > 50000;

A department qualifies if either its employee count is at least four or its average salary exceeds 50,000.

10.3 Combine AND, OR, and Parentheses

SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2
   AND (
       AVG(salary) > 45000
       OR MAX(salary) >= 60000
   );

Parentheses make the intended logic clear. The department must contain at least two employees and must satisfy at least one of the two conditions inside the parentheses.

11. Using WHERE and HAVING Together

You can use WHERE and HAVING in the same query. The WHERE clause first selects eligible rows, and HAVING then filters the groups created from those rows.

11.1 Example: Average Salary of IT Employees

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
WHERE department = 'IT'
GROUP BY department
HAVING AVG(salary) > 45000;

The WHERE clause includes only IT employees. MySQL calculates the average salary for that department, and HAVING keeps the group only if the average is greater than 45,000.

11.2 Example: Filter High Salaries Before Grouping

SELECT
    department,
    COUNT(*) AS qualifying_employees,
    AVG(salary) AS average_salary
FROM employees
WHERE salary >= 40000
GROUP BY department
HAVING COUNT(*) >= 2;

This query first removes employees with salaries below 40,000. It then groups the remaining records by department and returns only groups containing at least two qualifying employee records.

11.3 WHERE Versus HAVING in the Same Query

12. Using HAVING with GROUP BY Multiple Columns

Sometimes grouping by a single column is not enough. You may need to group by department and city, product and region, or customer and year. The HAVING clause can filter these more detailed groups.

12.1 Employee Count by Department and City

SELECT
    department,
    city,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department, city
HAVING COUNT(*) >= 2;

This query creates a separate group for every department-and-city combination. It returns only combinations with at least two employee records.

12.2 Average Salary by Department and City

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

The result shows department-and-city combinations whose average salary is greater than 40,000.

12.3 Monthly Sales by Product

SELECT
    DATE_FORMAT(sale_date, '%Y-%m') AS sales_month,
    product_name,
    SUM(quantity * unit_price) AS total_revenue
FROM sales_transactions
GROUP BY DATE_FORMAT(sale_date, '%Y-%m'), product_name
HAVING SUM(quantity * unit_price) > 50000
ORDER BY sales_month, total_revenue DESC;

This example assumes a table named sales_transactions with the columns sale_date, product_name, quantity, and unit_price. It returns monthly product groups whose revenue exceeds 50,000.

13. Using HAVING with Aggregate Aliases

An alias is a temporary name assigned to a calculated column in a query. MySQL permits aliases from the SELECT list to be referenced in HAVING in supported query forms. However, using the aggregate expression directly is clear and portable across SQL systems.

13.1 Using the Aggregate Expression

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

13.2 Using the Alias in MySQL

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING employee_count > 2;

MySQL supports this alias usage. When writing SQL intended for multiple database systems, consider repeating the aggregate expression in the HAVING clause for greater portability.

13.3 Avoid Ambiguous Aliases

Choose aliases that do not conflict with existing column names or grouping columns. Clear names such as total_sales, average_salary, and employee_count make queries easier to read and maintain.

14. Using HAVING with ORDER BY

The HAVING clause filters grouped results, while ORDER BY sorts the groups that remain. Use both when you want to display qualifying groups in a meaningful order.

14.1 Sort Departments by Average Salary

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

This query returns departments with average salaries above 40,000 and sorts them from the highest average to the lowest.

14.2 Sort Products by Total Revenue

SELECT
    product_name,
    SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY product_name
HAVING SUM(quantity * unit_price) > 20000
ORDER BY total_revenue DESC;

The result contains only products meeting the revenue threshold and displays the highest-revenue products first.

15. Using HAVING with LIMIT

The LIMIT clause restricts how many rows appear in the final result. When used with HAVING and ORDER BY, it can return the top qualifying groups.

15.1 Top Two Departments by Average Salary

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 35000
ORDER BY average_salary DESC
LIMIT 2;

MySQL first produces the grouped results, filters them with HAVING, sorts the qualifying results, and then returns up to two rows.

15.2 Top Three Products by Revenue

SELECT
    product_name,
    SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY product_name
HAVING SUM(quantity * unit_price) > 10000
ORDER BY total_revenue DESC
LIMIT 3;

This query returns up to three products that meet the revenue threshold, ordered by revenue from highest to lowest.

16. Filtering Duplicate Values with HAVING

The HAVING clause can help identify duplicate values by grouping records and retaining groups that appear more than once. This is a common data-cleaning technique.

16.1 Find Duplicate Employee Names

SELECT
    employee_name,
    COUNT(*) AS name_count
FROM employees
GROUP BY employee_name
HAVING COUNT(*) > 1;

This query returns employee names that occur more than once in the table. It can help identify possible duplicate records, although repeated names do not always mean that two records represent the same person.

16.2 Find Customers with Multiple Orders

SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

This query assumes an orders table with a customer_id column. It returns customers who have placed more than one order.

16.3 Find Products Appearing in Multiple Records

SELECT
    product_name,
    COUNT(*) AS record_count
FROM sales
GROUP BY product_name
HAVING COUNT(*) > 1;

This identifies products that appear in multiple sales records. The result can be useful for analysing transaction frequency.

17. Filtering Aggregate Results Without GROUP BY

Although HAVING is most commonly used with GROUP BY, a query containing aggregate functions without an explicit GROUP BY treats the qualifying input rows as one aggregate group.

17.1 Check Whether the Table Contains Enough Records

SELECT COUNT(*) AS total_employees
FROM employees
HAVING COUNT(*) >= 10;

This query returns a single row with the employee count if the table contains at least ten records. If the condition is false, it returns no rows.

17.2 Check the Overall Average Salary

SELECT AVG(salary) AS overall_average_salary
FROM employees
HAVING AVG(salary) > 40000;

This query returns the overall average salary only if the calculated average is greater than 40,000. Without an explicit GROUP BY, the entire qualifying input set is treated as one aggregate group.

18. Understanding NULL Values in HAVING Conditions

Aggregate functions handle NULL values differently depending on the function. For example, COUNT(*) counts rows, while COUNT(column_name), SUM(column_name), and AVG(column_name) ignore NULL values in that column. MIN() and MAX() also ignore NULL values.

18.1 Groups with No Non-NULL Salary Values

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

If a group has no non-NULL salary values, its average is NULL. The comparison with 40,000 is not true, so the group does not pass this condition.

18.2 Filter Groups Based on Available Values

SELECT
    department,
    COUNT(*) AS total_records,
    COUNT(salary) AS available_salaries
FROM employees
GROUP BY department
HAVING COUNT(salary) >= 2;

This query returns departments with at least two non-NULL salary values. It is useful when the requirement concerns the number of available measurements rather than the total number of rows.

19. Common Mistakes When Using HAVING

19.1 Using WHERE with an Aggregate Function

Incorrect query:

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
WHERE COUNT(*) > 2
GROUP BY department;

Why it is incorrect: WHERE filters individual rows before grouping and cannot directly use aggregate functions in its condition.

Correct query:

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

19.2 Using HAVING Instead of WHERE for Row Filtering

Less appropriate query:

SELECT employee_name, salary
FROM employees
HAVING salary > 40000;

Better query:

SELECT employee_name, salary
FROM employees
WHERE salary > 40000;

Use WHERE when the condition applies to individual records and no aggregate-based filtering is required. This is clearer and follows the intended purpose of the clause.

19.3 Forgetting GROUP BY

If you want to calculate one aggregate result per department, you must group the data by department. Otherwise, an aggregate query without GROUP BY generally produces one aggregate group rather than separate department results.

19.4 Confusing Total Count with Non-NULL Count

COUNT(*) counts every row, while COUNT(column_name) excludes rows where that column is NULL. Choose the function that matches the actual requirement.

19.5 Using the Wrong Comparison Operator

Conditions such as >, >=, <, and = produce different results. For example, HAVING COUNT(*) > 3 excludes groups with exactly three records, while HAVING COUNT(*) >= 3 includes them.

19.6 Selecting Unaggregated Columns Incorrectly

When using GROUP BY, select grouping columns and aggregate expressions, or columns that MySQL can determine are functionally dependent on the grouping columns. Avoid selecting unrelated non-aggregated columns, especially when ONLY_FULL_GROUP_BY is enabled.

20. Best Practices for Using HAVING

• Use HAVING primarily to filter groups based on aggregate calculations.
• Use WHERE to filter individual records before aggregation.
• Write the GROUP BY clause before HAVING.
• Use clear aliases for aggregate results.
• Choose the correct aggregate function for the business question.
• Use parentheses when combining multiple conditions with AND and OR.
• Use ORDER BY to make grouped reports easier to interpret.
• Use LIMIT when only a limited number of qualifying groups are required.
• Check whether NULL values change the meaning of your aggregate conditions.
• Test equality and boundary conditions carefully.
• Prefer explicit aggregate expressions in HAVING when portability across SQL database systems matters.
• Use indexes on columns involved in filtering and joins where appropriate, and inspect query plans for large datasets.

21. Practical Project: Department and Sales Analysis

Project objective: Build a summary report that identifies departments and products meeting specific business conditions using the HAVING clause.

Project Requirements

1. Find departments with more than three employees.
2. Find departments with an average salary above 45,000.
3. Find departments with total salaries greater than 100,000.
4. Find department-and-city groups containing at least two employees.
5. Find products whose total revenue exceeds 20,000.
6. Display qualifying departments ordered by average salary.
7. Return the top two departments after applying the aggregate conditions.

Solution 1: Departments with More Than Three Employees

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;

Solution 2: Departments with High Average Salary

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

Solution 3: Departments with High Total Salary

SELECT
    department,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

Solution 4: Groups by Department and City

SELECT
    department,
    city,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department, city
HAVING COUNT(*) >= 2;

Solution 5: Products with Revenue Above 20,000

SELECT
    product_name,
    SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY product_name
HAVING SUM(quantity * unit_price) > 20000;

Solution 6: Sorted Department Report

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

Solution 7: Top Two Qualifying Departments

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 35000
ORDER BY average_salary DESC
LIMIT 2;

22. Practice Exercises

Use the sample tables to practise writing queries with HAVING.

1. Find departments containing more than two employees.
2. Find departments with exactly two employees.
3. Find departments whose average salary is above 40,000.
4. Find departments whose total salary exceeds 150,000.
5. Find departments whose maximum salary is greater than 55,000.
6. Find departments whose minimum salary is at least 40,000.
7. Find department-and-city combinations containing at least two employees.
8. Find products with total revenue above 50,000.
9. Find customers who have placed at least three orders.
10. Display departments with at least two employees and an average salary above 40,000.
11. Sort departments with average salaries above 35,000 from highest to lowest.
12. Return the top three products by total revenue after filtering groups.

23. Practice Exercise Answers

Answer 1: Departments with More Than Two Employees

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

Answer 2: Departments with Exactly Two Employees

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) = 2;

Answer 3: Average Salary Above 40,000

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

Answer 4: Total Salary Above 150,000

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 150000;

Answer 5: Maximum Salary Above 55,000

SELECT department, MAX(salary) AS maximum_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 55000;

Answer 6: Minimum Salary at Least 40,000

SELECT department, MIN(salary) AS minimum_salary
FROM employees
GROUP BY department
HAVING MIN(salary) >= 40000;

Answer 7: Department-and-City Groups with at Least Two Employees

SELECT department, city, COUNT(*) AS employee_count
FROM employees
GROUP BY department, city
HAVING COUNT(*) >= 2;

Answer 8: Products with Revenue Above 50,000

SELECT product_name,
       SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY product_name
HAVING SUM(quantity * unit_price) > 50000;

Answer 9: Customers with at Least Three Orders

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3;

Answer 10: Multiple Aggregate Conditions

SELECT department,
       COUNT(*) AS employee_count,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2
   AND AVG(salary) > 40000;

Answer 11: Sorted Average Salary Report

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

Answer 12: Top Three Products by Revenue

SELECT product_name,
       SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY product_name
HAVING SUM(quantity * unit_price) > 10000
ORDER BY total_revenue DESC
LIMIT 3;

24. MySQL HAVING Interview Questions and Answers

Q1. What is the HAVING clause in MySQL?

Answer: The HAVING clause filters grouped or aggregated results based on specified conditions.

Q2. What is the main difference between WHERE and HAVING?

Answer: WHERE filters individual rows before aggregation, while HAVING filters groups based on aggregate conditions.

Q3. Can HAVING be used without GROUP BY?

Answer: Yes. In an aggregate query without an explicit GROUP BY, the input rows form a single aggregate group, and HAVING can filter that result.

Q4. Can aggregate functions be used in HAVING?

Answer: Yes. Aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() can be used in HAVING conditions.

Q5. Why can an aggregate function not be used directly in WHERE?

Answer: WHERE filters individual rows before grouping and aggregate calculations. Use HAVING when the condition depends on an aggregate result.

Q6. Can WHERE and HAVING be used together?

Answer: Yes. WHERE filters the rows first, and HAVING filters the groups formed from the remaining rows.

Q7. Can HAVING use multiple conditions?

Answer: Yes. Conditions can be combined using AND, OR, and parentheses.

Q8. Can HAVING be used with COUNT(*)?

Answer: Yes. For example, HAVING COUNT(*) > 3 returns groups with more than three rows.

Q9. Can HAVING use an alias?

Answer: MySQL supports referring to a select-list alias in HAVING. For portability across database systems, repeating the aggregate expression is often preferable.

Q10. Can HAVING be used with ORDER BY?

Answer: Yes. HAVING filters groups, and ORDER BY sorts the resulting groups.

Q11. What happens when an aggregate condition evaluates to NULL?

Answer: A condition that evaluates to NULL is not true, so the group does not satisfy that HAVING condition.

Q12. How do you find duplicate records using HAVING?

Answer: Group records by the column or columns being checked, then use HAVING COUNT(*) > 1 to find values appearing more than once.

Q13. Can HAVING be used with LIMIT?

Answer: Yes. It can filter groups before the final result is restricted by LIMIT.

Q14. What is the order of GROUP BY, HAVING, and ORDER BY?

Answer: The usual query order is GROUP BY, followed by HAVING, then ORDER BY, and finally LIMIT.

Q15. What is a common mistake when using HAVING?

Answer: A common mistake is using HAVING to filter ordinary row values when WHERE would be more appropriate, or using an aggregate condition inside WHERE.

25. Quick Revision Checklist

• HAVING filters groups after aggregation.
• WHERE filters rows before aggregation.
• GROUP BY creates groups based on one or more columns.
• COUNT() can filter groups based on record counts.
• SUM() can filter groups based on totals.
• AVG() can filter groups based on average values.
• MIN() and MAX() can filter groups based on minimum and maximum values.
• Use AND, OR, and parentheses for multiple conditions.
• ORDER BY sorts the qualifying groups.
• LIMIT restricts the final number of returned rows.
• NULL values can affect aggregate results and should be handled according to the data's meaning.
• Use clear aliases and avoid ambiguous column references.

26. Conclusion

The HAVING clause is an important MySQL feature for filtering aggregated data. It allows you to work with grouped information and return only the results that satisfy specific business conditions. By combining HAVING with GROUP BY, COUNT(), SUM(), AVG(), MIN(), and MAX(), you can build meaningful reports and perform detailed data analysis.

Remember the key difference: use WHERE for individual records and HAVING for aggregate conditions. Practising these queries will help you write clearer SQL and prepare for database projects, reporting tasks, and MySQL interviews.

Continue learning: Explore the MySQL Training Course for more database concepts and practical SQL exercises.

Course Demo: Register for a Course Demo to learn more about the training.

Official Reference: MySQL SELECT Statement and HAVING Clause Documentation