SQL HAVING Clause

The SQL HAVING clause is used to filter the results of an aggregated query. It works with aggregate functions like SUM, COUNT, AVG, MIN, and MAX, allowing you to set conditions on the result of these functions.

The HAVING clause is typically used in combination with the GROUP BY clause, whereas the WHERE clause is used for filtering rows before aggregation.

In this tutorial, we will go through SQL HAVING clause, its syntax, and how to use this clause in SQL statements, with the help of well detailed examples.

Syntax of SQL HAVING Clause

The basic syntax of the SQL HAVING clause is as follows:

</>
Copy
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

Each part of this syntax has a specific purpose:

  • SELECT: Specifies the columns and aggregate functions to retrieve.
  • FROM: Specifies the table from which to retrieve data.
  • GROUP BY: Groups the result set by one or more columns.
  • HAVING condition: Filters the grouped data based on an aggregate function condition.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the HAVING clause in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample grades table with fields student_id, subject, and score.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a grades table:

</>
Copy
CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    score INT
);

4. Insert sample data into the grades table:

</>
Copy
INSERT INTO grades (student_id, subject, score)
VALUES
(1, 'Math', 85),
(1, 'Science', 90),
(2, 'Math', 78),
(2, 'Science', 80),
(3, 'Math', 92),
(3, 'Science', 95),
(4, 'Math', 70),
(4, 'Science', 85);