MySQL HAVING Clause

Introduction

In this chapter, we will learn about the HAVING clause in MySQL. The HAVING clause is used to filter groups of rows created by the GROUP BY clause based on a specified condition. Unlike the WHERE clause, which filters rows before grouping, the HAVING clause filters groups after they have been formed. This is particularly useful for applying conditions to aggregate functions. We will cover the syntax, examples, and important considerations for using the HAVING clause.

Syntax

The basic syntax for the HAVING clause is:

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

• column1: The column by which you want to group the results.
• aggregate_function(column2): The aggregate function to apply to the grouped data.
• table_name: The name of the table from which to select data.
• condition: The condition to filter the rows (optional).
• HAVING aggregate_function(column2) condition: The condition to filter the groups.

Using HAVING

Example with COUNT

SELECT last_name, COUNT(*) AS count
FROM students
GROUP BY last_name
HAVING COUNT(*) > 1;

This example selects last names that appear more than once in the students table.

Example with SUM

SELECT last_name, SUM(score) AS total_score
FROM students
GROUP BY last_name
HAVING SUM(score) > 100;

This example selects last names where the total score is greater than 100.

Example with AVG

SELECT last_name, AVG(score) AS average_score
FROM students
GROUP BY last_name
HAVING AVG(score) > 80;

This example selects last names where the average score is greater than 80.

Example with MAX

SELECT last_name, MAX(score) AS highest_score
FROM students
GROUP BY last_name
HAVING MAX(score) > 85;

This example selects last names where the highest score is greater than 85.

Example with MIN

SELECT last_name, MIN(score) AS lowest_score
FROM students
GROUP BY last_name
HAVING MIN(score) < 80;

This example selects last names where the lowest score is less than 80.

Full Example

Let's go through a full example where we create a table, insert data into it, and use the HAVING clause to filter groups.

1. Create a Database:

CREATE DATABASE school;

2. Select the Database:

USE school;

3. Create a Table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE,
    score INT
);

4. Insert Data into the Table:

INSERT INTO students (first_name, last_name, email, enrollment_date, score) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01', 85),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02', 90),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03', 75),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04', 95),
('Sahil', 'Mehta', 'sahil.mehta@example.com', '2023-07-05', 80),
('Rahul', 'Sharma', 'rahul.sharma2@example.com', '2023-07-06', 88);

5. Use the HAVING Clause with COUNT:

SELECT last_name, COUNT(*) AS count
FROM students
GROUP BY last_name
HAVING COUNT(*) > 1;

Output

last_name	count
Sharma	2

6. Use the HAVING Clause with SUM:

SELECT last_name, SUM(score) AS total_score
FROM students
GROUP BY last_name
HAVING SUM(score) > 100;

Output

last_name	total_score
Sharma	173
Verma	95
Singh	90

7. Use the HAVING Clause with AVG:

SELECT last_name, AVG(score) AS average_score
FROM students
GROUP BY last_name
HAVING AVG(score) > 80;

Output

last_name	average_score
Sharma	86.5
Verma	95
Singh	90
Mehta	80

8. Use the HAVING Clause with MAX:

SELECT last_name, MAX(score) AS highest_score
FROM students
GROUP BY last_name
HAVING MAX(score) > 85;

Output

last_name	highest_score
Sharma	88
Verma	95
Singh	90

9. Use the HAVING Clause with MIN:

SELECT last_name, MIN(score) AS lowest_score
FROM students
GROUP BY last_name
HAVING MIN(score) < 80;

Output

last_name	lowest_score
Kumar	75

Important Considerations

• Order of Clauses: The HAVING clause is used after the GROUP BY clause and before the ORDER BY clause (if used).
• Filtering Groups: Use the HAVING clause to filter groups based on aggregate functions. Use the WHERE clause to filter rows before grouping.
• Performance: Using HAVING can impact performance, especially on large datasets. Ensure appropriate indexing and optimize queries to improve performance.

Conclusion

The HAVING clause is used for filtering groups of rows based on aggregate functions in MySQL queries. This chapter covered how to use the HAVING clause with various aggregate functions, provided examples, and discussed important considerations.