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:
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:
CREATE DATABASE school;2. Select the school database:
USE school;3. Create a grades table:
CREATE TABLE grades (
student_id INT,
subject VARCHAR(50),
score INT
);4. Insert sample data into the grades table:
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);
Examples: Using HAVING Clause in Queries
Now, let’s explore different scenarios of using the HAVING clause with this table.
1. Filtering Groups with a Minimum Average Score
To select subjects where the average score is 80 or more:
SELECT subject, AVG(score) AS avg_score
FROM grades
GROUP BY subject
HAVING AVG(score) >= 80;This query groups the records by subject and then filters them to include only subjects with an average score of 80 or higher.
2. Filtering Groups Based on Total Score
To find subjects with a total score of 150 or more:
SELECT subject, SUM(score) AS total_score
FROM grades
GROUP BY subject
HAVING SUM(score) >= 150;This query groups the data by subject and includes only subjects with a total score of 150 or more.
3. Using HAVING with COUNT to Filter by Group Size
To find subjects with more than one student record:
SELECT subject, COUNT(*) AS num_records
FROM grades
GROUP BY subject
HAVING COUNT(*) > 1;This query groups by subject and filters to include only those subjects with more than one record.
Reference: SQL COUNT Function
4. Combining WHERE and HAVING Clauses
To find subjects where individual scores are over 75 and the average score for the group is 85 or higher:
SELECT subject, AVG(score) AS avg_score
FROM grades
WHERE score > 75
GROUP BY subject
HAVING AVG(score) > 85;This query first filters for records where scores are over 75 using the WHERE clause, then groups by subject, and finally filters by average score using HAVING.
Reference: SQL WHERE Clause
FAQs for SQL HAVING
1. What does the SQL HAVING clause do?
The HAVING clause filters groups based on aggregate function conditions, such as averages or totals, in queries using GROUP BY.
2. How is HAVING different from WHERE?
WHERE filters rows before aggregation, while HAVING filters after aggregation, allowing conditions on aggregate functions.
3. Can I use HAVING without GROUP BY?
Yes, HAVING can be used without GROUP BY, but it’s typically used in grouped queries to filter aggregated results.
4. Can HAVING include multiple conditions?
Yes, you can use multiple conditions with HAVING, combining them with AND or OR operators.
5. Does HAVING consider NULL values?
Yes, HAVING considers NULL values in aggregated results, so they can be part of the condition if applicable.