`HAVING` Clause in DBMS (MySQL) [ English ]

Languages

• English

Introduction

When working with grouped data in SQL, it is sometimes necessary to filter the groups based on certain conditions. While the WHERE clause filters rows before grouping, the HAVING clause filters groups after the GROUP BY operation.

The HAVING clause is mainly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to specify conditions on grouped data.

Definition

The HAVING clause is used in SQL to apply conditions on grouped records created using the GROUP BY clause.

It filters groups based on aggregate values.

Syntax

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

Where:

• column_name – The column used for grouping.
• aggregate_function() – A function applied to grouped data.
• condition – The condition used to filter the grouped results.

Example Table

Suppose we have a table named students.

Example 1: Using HAVING with COUNT

SELECT course, COUNT(*)
FROM students
GROUP BY course
HAVING COUNT(*) > 1;

Output

This query groups students by course and displays only the courses that have more than one student.

Example 2: Using HAVING with AVG

SELECT course, AVG(age)
FROM students
GROUP BY course
HAVING AVG(age) > 20;

Output

This query shows courses where the average age of students is greater than 20.

Difference Between WHERE and HAVING

Example:

SELECT course, COUNT(*)
FROM students
WHERE age > 19
GROUP BY course
HAVING COUNT(*) > 1;

Here:

• WHERE filters rows before grouping.
• HAVING filters grouped results.

Key Points

• HAVING is used to filter grouped records.
• It is commonly used with aggregate functions.
• It is used together with the GROUP BY clause.
• It works after grouping of data.

Summary

The HAVING clause is used to apply conditions on grouped data in SQL queries. It is especially useful when working with aggregate functions and grouped records. By filtering grouped results, the HAVING clause helps users analyze summarized data more effectively.